an Analysis problem

kante

I have a web site for publications...

these pubs are of various types (e.g. news, blog, video)
all the publication share some charateristics...

so i thought to implement de DB design and OOP like having
a db table which contains all publication common details then have different table for the Type specific charateristics. (In oop having a parent Pub class and N diff class extending and adding type specific functionalities properties)

this for having ordering and searching optimized...

Now i'm thinking to go back , and have all publication data divided:
newses has mysql table, blog has anothe one... in other words no more the big single table with common attributes

Why is that? because i'm facing the problem to make inner joins many times... between
the publication big table and the news specific table for example...

what do you suggest? should i convert the data structure? or what i did is better?

i wish i can find an online analysis of this common( I believe ) problem

Many thanks...

knowj

Can you provide an example database/table structure and data.

Generally the idea is to prevent data replication across the database/databases whilst not creating performance issues or bottle necks.

Try reading up on Normalization for a better understanding if I understand your problem: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

NogDog

You might also want to consider the Decorator pattern for you classes/objects as opposed to inheritance. I'm not saying it's necessarily better, but worth considering to see which approach will better serve your needs.

kante

knowj;10970212 wrote:
Can you provide an example database/table structure and data.

Generally the idea is to prevent data replication across the database/databases whilst not creating performance issues or bottle necks.

Try reading up on Normalization for a better understanding if I understand your problem: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Actual situation :

News and Blog are both of publication type, so in MySQL i have:

tbl publication{idpub, section, title, subtitle, author... }

#section is ENum ('news', 'blog') and idNews and IdBlog refer to idpub in other tables

tbl News {idNews, htmlText, template, iconurl...}

tbl Blog{idBlog, theme, topic...}

#the specific fields of News and Blog above are just for gratia exempli.... not really matter now

a Different approach :

no tbl publication

and have
tbl News {idpub, title, subtitle, author, htmlText, template, iconurl...}
tbl Blog {idpub, title, subtitle, author, theme, topic...}

as u can see the fields idpub, title, subtitle, author, before where grouped in one father table now separated in different table

in the first approach queries for order and 1st level searchin are easier
but if u like to select more than common details (the ones that are on the specific tables News and blog) u have to perform different queries to have everything...
or one big union Select with many null values...

i hope this help to understand my situation

NogDog

You should still be able to use just one query in the design with all articles in the Publication table by using a JOIN on the other relevant table.

kante

NogDog;10970264 wrote:
You should still be able to use just one query in the design with all articles in the Publication table by using a JOIN on the other relevant table.

of course i can... the issue here isn't about feasibility... both solution work fine... and none of them have redoundancy in data (maybe the second solution has redoundancy in structure)

my question is about what is more practical to be managed by code...

NogDog

kante;10970286 wrote:
of course i can... the issue here isn't about feasibility... both solution work fine... and none of them have redoundancy in data (maybe the second solution has redoundancy in structure)

my question is about what is more practical to be managed by code...

Maybe I misunderstood what you meant when you wrote:

in the first approach queries for order and 1st level searchin are easier
but if u like to select more than common details (the ones that are on the specific tables News and blog) u have to perform different queries to have everything...
or one big union Select with many null values...