Database - which method is better?

[+Nik Louch Subscriber²]

I am designing a database structure for a simple solution, and am being suggested to change my existing layout. I have argued my layout is "cleaner" but being told I have needless tables...

Say I have a product table. I also need to store long text fields of description and internal_review.

My way involves a main product table, with a table each for description and internal_review, both joining against a common product_id field.

The way being suggested is as above, but with a single product_texts table, with a field for description and a field for internal_review, again joining on product_id.

Does anyone have any feelings either way on this?

I think I just like the clean separation, but can see benefit in the suggested way as far as efficiency goes...

[Prince Charming]

I don't really see your way as cleaner, and the bottom example does seem the way I would go about doing it. Sorry if that's not what you wanted to hear, but you do have needless tables.

[testroom_9]

urm, the second way is how i would do it, i wouldnt want two tables for things of the same nature.

the entire ethos of a database is to group together things of a similar nature - plus it means less table creates....

also you have kinda answered your question by having a SQL select next to them - the suggestion is clearly more simple

[The2]

I'd go your way. It's self explanatory

[+Nik Louch Subscriber²]

Thanks for the replies. I am not trying to justify my way, am looking for reasons to go the way suggested, which seems to be efficiency mroe than anything :)

[testroom_9]

its not just efficiency, its good practice. it may well be that you know its never going to change, but what if it did and you needed to expaned to:

store multiple reviews

add new long text fields

yada yada

its going to get real messy real fast if you have a table per field. the idea of a datbase table is to have records for entity avaliable, if you had to expand what the db did at some point it would be very bad to have to write 100 joins for each extra bit of information.

as i said that might not be relevant.

what is it for can i ask?

[+Nik Louch Subscriber²]

I take that onboard and see where you are coming from. I am pretty much sold on the the second idea.

It's a database of products.

[testroom_9]

lol, yeh obviously. just didnt know weather it was a uni thing or something of the sort... either way... one to many FTW

[+Nik Louch Subscriber²]

Cheers bud! As far as reviews go, this is an internal review - we test each product and write a review. It is a one to one relationship.

When it comes to user reviews, that will be a one to many, so I will give that it's own table for sure.

[atrotter01]

Why give user reviews their own table? Just use the same review table again, and have a tinyint field to flag rather or not it's a user review (1) or not (0)?

Cheers bud! As far as reviews go, this is an internal review - we test each product and write a review. It is a one to one relationship.

When it comes to user reviews, that will be a one to many, so I will give that it's own table for sure.

[+Nik Louch Subscriber²]

Because I would also like to include the user ID, it will be a one-to-many as opposed to one-to-one relationship, a lot of reasons.

[lnmnky]

There could be more than one review, so you need a review_id also.

In that situation you would do the first way.

[atrotter01]

I would still put them in the same table, and just put the user id in that table but allow it to be null. For that matter, you can then make the assumption that if the user id is null, it is your internal review, and you don't even need the tinyint field.

Ah.. sorry, I was going about this from the stand point of "My Way", not the suggested way that you are leaning toward. You are right, with the suggested way it just wouldn't make sense, because you would potentially be duplicating other information needlessly :)

Will a product ever have more than one description? If not, the way I would personally do this is to move the description into the main products table, and then have a single review table that will hold both user reviews and staff reviews.

Edited November 24, 2009 by Drakja

[Hot]

Simply put, if there will ever by multiple reviews then your way is better. If there will only ever be a single review per product, which I suspect could be the case as I notice you actually referred to the field as internal_review, then their way is better.

With multiple reviews, your way would eliminate having a blank or redundant description field for every review row. That is better, because redundancy is a dangerous to data integrity (which is the most important aspect of database design).

However, with only single reviews per product, your suggestion would effectively be referred to as vertical partitioning, and it will make your system slower, not more efficient. In fact if this was the case then I would eliminate additional tables altogether, because I'm pretty sure a product is only ever going to have one description, too.

Joins are not fast, joins are slow. Selecting from a single table is much faster and if you structure your statements properly -- that is, specify the columns you want explicitly instead of using "SELECT *" -- then there should be no overhead from additional columns at all.

[lnmnky]

Then you may as well put them all in one table if they are all single reviews.

Proper way:

Multiple reviews should have their own table linked by product_id

Descriptions which I assume are single, should just be part of the main table.

[+Nik Louch Subscriber²]

My reasoning for is in an external table is because it is a TEXT field, and I'd always been taught that having a TEXT field within a table of other types was a slow way to use a database, and that bumping them off to another table was preferable and then joining.

Am I wrong? I don't hold on to any of my database knowledge with any unproven convictions and am happy to relearn.

[atrotter01]

I've never used this approach, and can't really see the benefits to be honest. A join is going to slow the query down, even if it's by a small fraaction of a second. I don't think you are really going to gain anything by putting text fields in their own table. Rather they are in their own table or the same table as the product, you are still going to have to access them, might as well be in one table so that you don't have the extra overhead of opening another :)

Also another thing to remember from a db-admin prespective is indexes. In one table, the DBMS only needs to keep up with one index, the primary key. Split into two just for moving text fields into their own table, it now has to keep up with the primary key on the products table, the primary key on the description table, and the foreign key back to the product table.

My reasoning for is in an external table is because it is a TEXT field, and I'd always been taught that having a TEXT field within a table of other types was a slow way to use a database, and that bumping them off to another table was preferable and then joining.

Am I wrong? I don't hold on to any of my database knowledge with any unproven convictions and am happy to relearn.

[CentralDogma]

Maybe I'm seeing something different then everyone else, but why not store everything in one table if every product only has one description and one review. If a product can have multiple reviews, then I would move reviews into their own table.

[XerXis]

your way is normalized one needless level too much

[_kane81]

Simply put, if there will ever by multiple reviews then your way is better. If there will only ever be a single review per product, which I suspect could be the case as I notice you actually referred to the field as internal_review, then their way is better.

With multiple reviews, your way would eliminate having a blank or redundant description field for every review row. That is better, because redundancy is a dangerous to data integrity (which is the most important aspect of database design).

However, with only single reviews per product, your suggestion would effectively be referred to as vertical partitioning, and it will make your system slower, not more efficient. In fact if this was the case then I would eliminate additional tables altogether, because I'm pretty sure a product is only ever going to have one description, too.

Joins are not fast, joins are slow. Selecting from a single table is much faster and if you structure your statements properly -- that is, specify the columns you want explicitly instead of using "SELECT *" -- then there should be no overhead from additional columns at all.

+1

[Rohdekill]

Just How many descriptions of a product do you need? Personally, I would have placed description within the Products table as it (usually) is a 1-1.

The reviews are 1-many, that's the only separate table I would have.