MySQL Extra Tables vs Type Field

[jimmyoneshot]

I'm wondering about best practice when creating a MySQL database.

 

I basically have 3 types of content - news, events, updates.

 

The thing is each of these tables all have the exact same column except news has a unique column of it's own and so does updates like this:-

 

NEWS

 

id

date

title

content

image

expiry_date

 

EVENTS

 

id

date

title

content

image

 

UPDATES

 

id

date

title

content

image

update_type_name

 

My question is would it be best to combine all of these into one table and use a type field to determine whether the item is a news item, event or update?

 

The problem here would be that there would be blank fields in the expiry date and update_type_name columns. Is this bad practice in db design?

 

I suppose for the front end user multiple tables would be better as this means that less records are being queried right?

[fenway]

Actually, the "best" way is to have a fourth table, as a parent, that contains all shared columns, and then use those 3 tables for extras, per type.

[jimmyoneshot]

Thanks Fenway. I've heard a lot of conflicting arguments on this.

 

So basically if tables have a large amount of similar fields they should be grouped together and have tables that contain extra details for each type? Got it.

 

So is it always bad practice to have fields which are going to contain empty rows? So considering the following example of a users table:-

 

user_id

first_name

surname

address

phone_number

mobile_number

 

If in the above example phone_number and mobile_number were optional and the rest were mandatory would it be best to split them 2 off into another table like this:-

 

PHONE_NUMBERS

 

id

user_id

number_type (mobile or regular phone)

phone_number

 

Or would that just be a bit extreme? The problem I can foresee with this is that when a new user is created the user record must be created, then the user_id of that last created record must be retrieved and then the phone number must be inserted which is 3 server calls as opposed to 1 from having everything in the same table.

 

So is this a case of something being good for best DB practice but bad for front end experience or am I over-thinking?

 

Sorry for the extra questions, it's just something I wanted to get clear.

[ebmigue]

Read about database normalization. Your problem is addressed by that science.

 

Avoid attribute names that are the same across tables/relvars, unless they refer to the same thing.

Otherwise, you'll have ambiguity across your queries, and program in general.

 

Hope it helps.

[fenway]

Taking DB normalization to it's logic end is often overkill.  I see nothing wrong with phone_number and mobile_number together -- unless, of course, you plan on allowing 3 of each, in which case another table is most definitely required.

[ebmigue]

@fenway

Taking DB normalization to it's logic end is often overkill.

Please refer us to your basis for saying that. Otherwise, you are misleading people -- and that is irresponsible.

 

 

@OP

 

Actually, no one can help you w/ regards to database design but the science of db normalization, your knowledge of the requirements, and some amount of experience (or intuition as some would call it).

 

All we can do is suggest. There is hardly a "right" suggestion. You alone will determine it, if it is within your requirements. And the science of db normalization is usually a helpful guide in achieving your requirements.

 

I'm saying this because you might get the impression that we are giving "absolute answers." Just to be sure, so that I won't run risk of misleading you.

 

Hope it helps.

[fenway]

4NF, 5NF, BCNF, and often even 3NF is not necessary for working with real-world data.

 

3NF is designed to eliminate duplication of data (removing columns that are not dependent upon the PK)-- generally, elimination is too black-and-white, and reduction is sufficient.

 

All NFs are on a spectrum -- whatever suits your needs is just fine -- though 1NF and 2NF are rarely sufficient.

 

 

[holophrastic]

@jimmy: So basically if tables have a large amount of similar fields they should be grouped together and have tables that contain extra details for each type? Got it.

 

I wouldn't say it like that.  when you say "a large amount of similar fields" what you mean is they "are very similar in nature".

 

In english, it's really easy to describe totally different things in similar words -- very generic words.  That's never the idea here.

 

If news and events in your project are pretty well the same thing, then certainly, having them share a table or a parent table is a great idea because you get to share functionality between them.  But if they are actually different things in your project, then having them share anything will just get in your way.

 

The questions you need to ask of your data are very simple.  Are the news and events "accidentally" the same, or are they the same for a real reason?  Just because two things are the same, doesn't mean that they'll stay the same. Hence the second question: Do you want upgrades to news to directly affect upgrades to events?  If you want them to grow in exactly the same direction, then they should share a table.  If you want them to grow similarly, then they should share a parent.  If you want them to have lives of their own, then they should be their own tables.

 

There's nothing wrong with two identical tables being their own tables, if they really are different.  Don't feel pressured to save tables, they're free.  Don't feel pressured to make everything look the same, that's the database's job -- it'll all be stored in the same mysql format no matter what you do.

 

The separations should serve you -- one way or the other.

[ebmigue]

4NF, 5NF, BCNF, and often even 3NF is not necessary for working with real-world data.

State your reliable basis/reference for saying that. Things as crucial as this should not be based on mere baseless opinion.

[fenway]

4NF, 5NF, BCNF, and often even 3NF is not necessary for working with real-world data.

State your reliable basis/reference for saying that. Things as crucial as this should not be based on mere baseless opinion.

Opinion, yes -- baseless, no.

 

Besides, why will you acccept someone else's opinion as a "reference", but not mine?

[jimmyoneshot]

Thanks for all the answers guys. Very thought provoking. I understand normalisation quite well but still struggle to figure out which situations it should be applied to the extreme and which it should not.

 

For example in my site I have 3 types of content photos, albums and updates. Each of these has their own table.

 

I want to be able to allow users to "like" each of these items but each user can only like each item once. To allow this I have a likes table acting as a join table between users and the photos/albums/updates tables with the following fields:-

 

content_id (the id of the content being liked)

content_type (the type of content being liked - 1: photo 2: album 3: update)

user_id (the id of the user that has liked the album)

 

When taking into consideration both front end and back end performance/other issues is this the most efficient way of doing this or should I have a likes table for each type of content i.e. photo_likes, album_likes and update_likes and get rid of the content_type field?

[ebmigue]

4NF, 5NF, BCNF, and often even 3NF is not necessary for working with real-world data.

State your reliable basis/reference for saying that. Things as crucial as this should not be based on mere baseless opinion.

Opinion, yes -- baseless, no.

 

Besides, why will you acccept someone else's opinion as a "reference", but not mine?

What is the basis then? Please post here.

 

I accepted theirs, because they have their basis. Their basis's basis also has their basis - w/c brings them back again to the leading authors.

Why accept the views of the leading authors? No, you just don't accept it. We have to examine it too. That's why we need your basis. Your reasons, in detail.

[jimmyoneshot]

Oh no I've started a war   

 

I think in relation to my above post I am going to go ahead with having like tables for every single content type as after doing some research I've heard that partitioning the database in this way is more efficient for the users.

 

If anyone can offer a different approach or reason why this isn't the case please let me know.

[fenway]

Thanks for all the answers guys. Very thought provoking. I understand normalisation quite well but still struggle to figure out which situations it should be applied to the extreme and which it should not.

@ebmigue -- this is exactly what I'm talking about.  Theory has no use without practical application.

 

I want to be able to allow users to "like" each of these items but each user can only like each item once. To allow this I have a likes table acting as a join table between users and the photos/albums/updates tables with the following fields:-

 

content_id (the id of the content being liked)

content_type (the type of content being liked - 1: photo 2: album 3: update)

user_id (the id of the user that has liked the album)

The only problem with this is that you won't be able to join easily, since MySQL won't let you use dynamic table names.

[ebmigue]

@ebmigue -- this is exactly what I'm talking about.  Theory has no use without practical application.

 

To me that is theory's merit.

 

It encourages people to think and seek further.

 

It broadens their perspective.

 

It enables them to decide with good information.

 

And not just accept some "dogmas and tenets" that might not be very wise considering their particular circumstance.

[ebmigue]

Oh no I've started a war   

I don't want to call it that way. War implies hostility. What we are doing is a typical exchange of ideas.

 

I think in relation to my above post I am going to go ahead with having like tables for every single content type as after doing some research I've heard that partitioning the database in this way is more efficient for the users.

If you do so (i.e., partition), ensure that the partition is non-loss. Meaning, when the partitioned tables are combined again (either by JOIN or by UNION) the original table - w/c is your requirement - is still achieved.

 

If anyone can offer a different approach or reason why this isn't the case please let me know.

 

Frankly, it is impossible to suggest a "smart" approach, since it is only you who know the complete story of your situation.

Of course, if you'd go about them in detail here...well, someone might be willing to help still. But less likely - since that would mean they are doing most of the job that is originally intended for you.

 

Thank you and hope it helps.

[fenway]

Except no one who posts on this forum wants their website to work "in theory" alone.

[ebmigue]

I am not suggesting that their websites should work "in 'theory alone'."

 

That would be quite unrealistic.

 

I'm suggesting that if theory could be used in building websites - and all the desirable consequences from it, then why not?

[holophrastic]

Because there is additional effort to using theory in building websites, and I'm telling you that additional effort isn't beneficial enough to anyone when compared to the time, effort, and resources required to do so.  That's why.  For every added effort, there is a cost, and there is a benefit. 

 

Here's your theory for you.  If you benefits don't out-weigh the costs, you shouldn't expend the efforts. 

 

See, now that's theory.  And after 15+ years of building web-sites, I can tell you quite expertly that it's not worth even considering theory when

building web-sites.  The following reasons are first to come to mind. 

 

- web-sites are virtual, and central, so they can be changed/fixed/corrected/adjusted at any time.  so mistakes aren't permanent.

 

- new technologies appear frequently, so old code gets replaced anyway, even if it was "right" when it was written.

 

- it's a very new industry of 20 years old, the modern web is only 10 years old.  most of the theories aren't established/perfected/proven.

 

- the market/industry/consumer-base/client-base shifts so radically that needs and requirements and ratios change sporadically with each project. so any attempt to create an equivalency across projects is futile, or just plain incorrect.

 

[ebmigue]

All your claims may be true.

 

But then the internet and websites will not come into existence if there were no theories in the first place (comp. sci).

 

One can go on claim that he doesn't need theory as one likes.

 

I have no problem with that.

 

Because whether or not one likes it, the fact remains: all developments in technology are impossible w/o theory.

 

 

Perhaps you take my posts as asking people to be "relational theoreticians" through and through.

 

No I am not asking that, nor am I asking anyone here.

 

It would quite impractical to demand that, I agree.

 

Besides, as you said, the costs of learning outweigh the benefits of delivering.

 

But that is still an open question; perhaps in the long run discarding theory will prove to be costly.

Maybe. I don't want to wager that possibility.

 

I am simply stating my POVs w/c everyone of us has.

[holophrastic]

You can say "maybe" all you like.  But in this case, it's a definite "no".  We're not talking about "in the first place".  We're talking about now.

[ebmigue]

You can say "maybe" all you like.  But in this case, it's a definite "no".

If you are a clairvoyant, then maybe I will believe you. But you are not. So, your conclusions about the future are baseless; and it is a definite "no" that I'd believe you.

 

We're not talking about "in the first place".  We're talking about now.

 

I would claim that "now" would not even be possible if there was no "in the first place."

"In the first place" is as important as "now." If it is not, why write things? Why commit things to memory?

 

Present problems can only be solved if we looked at previous experiences of individuals, and how they approached a similar problem. Theory formalises such approach.

[fenway]

Yes, theory is one way to approach a problem (e.g. by formalizing it).  But you have inherently presupposed that this is somehow required, and it's not.

 

Your supposition that "now" would not even be possible if there was no "in the first" place is flawed, because you're extrapolating.  Yes, we needed there to be some "first place" in the past, but that's not necessary to move ahead at every step.  If no new theory was advanced for the next 50 years, websites would all still be working, with 10 year old code to boot.

[ebmigue]

Yes, theory is one way to approach a problem (e.g. by formalizing it).  But you have inherently presupposed that this is somehow required, and it's not.

Yes, I'd agree. Not all problems need theory for its solution. Think of problems w/ regards to your wife/girlfriend, for instance.

 

But we are talking about Relational Databases here people.

 

If DBMS implementers didn't have an idea about relational theory, they wouldn't even have produced their products, their SQL-based products.

 

DBMS vendors and users are like calculator manufacturers and users, respectively.

 

If you want to effectively manufacture a calculator, you need knowledge of arithmetic.

 

If you want to effectively manufacture a relational DBMS, you need knowledge of relational theory.

 

If you want to effectively use a calculator, you need knowledge of arithmetic.

 

If you want to effectively use a relational DBMS, you need knowledge of relational theory.

 

They keyword there is "effectively".

 

They need theory. Period.

 

Again, if someone is able to manufacture or use a relational DBMS (or calculator) "effectively and efficiently", then it is because either of these causes: intuition (because of previous experience); pure luck/guess.

 

And yet there is another way to use it: learn theory.

 

By "learn theory" I do not mean that you turn yourself like a scholar and all the stereotypes associated w/ being a scholar.

 

You only need passing acquaintance. Just the basics. Much like learning arithmetic will enable you to live in a practical world.

 

Devalue and undermine such suggestion all you want, but I'm not writing for those people anyway. I am writing for people that might find these advices sensible.

 

Your supposition that "now" would not even be possible if there was no "in the first" place is flawed, because you're extrapolating.

I don't understand that.

 

Yes, we needed there to be some "first place" in the past, but that's not necessary to move ahead at every step.  If no new theory was advanced for the next 50 years, websites would all still be working, with 10 year old code to boot.

You see, I don't want to go into that argument in the first place; I was dragged into it.

 

It's a chicken and egg problem. (Why? Exercise for the reader)

 

Yes, theory has its "formal" part. For the most part, it is not needed.

 

But theory too has its "intuitive" parts. That is what is important. The basic axioms/definitions. The basic theorems derived from those axioms.

[fenway]

"manufacturing" and "using" do not require the same skill set.