Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a solution and I have a question.

All my data is currently stored in a unique table. Each row of data is associated with a type.

My question is, what's the best practice for defining a table structure: all data in an unique table with associated type or data stored in dedicated type table?

eg: how to store people and associated colors.

  • all data in an unique table like id, people_id, color_id
  • data stored in table dedicated to color:
    • table blue: id, people_id
    • table red : id, people_id

In my opinion, it's better to have everything in a single table to avoid complex queries, but I'm scared about query time with billions of rows.

share|improve this question

migrated from stackoverflow.com May 20 '13 at 17:22

This question came from our site for professional and enthusiast programmers.

    
When looking at this think about the amount of changes that will need to be done if a new color is added or removed. With the first method you will just need to update two tables. However with the second method you would have to create an entire new table or drop a table to remove the color. –  Joe W May 20 '13 at 15:17
add comment

6 Answers

I would go for the first option otherwise you will be changing your database every time you want to add a new color. It would be a nightmare.

Also, if you ever needed to select for multiple colors you would have to join to the various tables, which isn't terrible, but is needlessly complex.

If you are using SQL Server, you might look at partitioning and see if it fits your needs.

share|improve this answer
add comment

modern sql databases should handle such large tables, where you got all data in one. and its faster than doing joins on a huge data sets.

tip: maybe you might want to take a look at graph databases which don't have problems with duplicate data (like the color which will be repeated across rows)

share|improve this answer
add comment

Hard to say from here, all depends on what sort of queries you are doing. If you put everything in one table and there are a lot of changes, optimising your indexes will be a big thing, if you split them up aside from having more complex code, something will have to 'know' blue is in blue table, then optimising joins will be.

Personally I'd start with option one, and see if your indexes get too big / too messy to deal with. The other way looks like premature optimisation to me.

share|improve this answer
add comment
up vote 0 down vote accepted

There are 3 common contemporary database designs:

1) Most database design is based on normalization, so have a look at that.

2) When that has performance problems denormalization is a common route.

3) When vast amounts of data are projected database design has moved away from these techniques (1 and 2 are two sides of the same coin) and new technologies have been used, for example have a look at noSQL.

Unless you are dealing with seriously vast amounts of data you'll probably want to start with 1) and possibly look at 2) if and when you have performance issues.

"Everything in a single table" is generally a bad idea as that table becomes huge, with a great deal of contention on it - every part of the application using it constantly. This would lead to deadlocks, for example, making queries slow.

share|improve this answer
    
i agree with you. In fact it's already what i'll do. historical data are store in nosql, this question concerns tables which are frequently updated with last collected value. –  gorjuce May 20 '13 at 15:23
add comment

You should read up on database normalization, this will help you to achieve best practices for YOUR specific situation (a few simple steps make it much easier).

As a side note, in terms of performance, more tables is usually better as you end up loading a lower number of tuples, but this can make query writing more complex. You should start by normalizing and creating a strong relational integrity and only then worry about performance concerns.

share|improve this answer
3  
"more tables is usually better.." is a gross oversimplification that easily leads beginners to create horribly non-relational designs (like the second one the OP proposed). Let them figure out Normalization first. Only when they understand that completely should they start to worry about the performance of the design (as opposed to the performance of the implementation, which is a different thing). –  RBarryYoung May 20 '13 at 15:24
    
I said in terms of performance and that much is fairly true, if you have fourteen tables for user data, each one storing different parts of a user's data that should all be in one table, you will probably improve performance, but, as I also said you will make query writing an awful lot more difficult. I think when taken holistically my answer is pretty accurate, though of course if you take a quote out of context it will seem wrong. –  Oshawott May 20 '13 at 15:28
3  
You present Normalization and Performance as co-equal and independent things in database design. They are neither. The first step in good design is always Normalization, because that's how you make sure that it's a correct design. Not coincidentally, the first step in Performance is also a normalized design. The number of tables has nothing material to do with either of these until much later in the process. Worrying about the number of tables during the design process is a recipe for bad designs, especially for someone who is unsure of what they are doing. –  RBarryYoung May 20 '13 at 15:35
    
@RBarryYoung - can see why you'd think that, though it wasn't the message I was trying to put across. Edited the post to make clear my intention. –  Oshawott May 20 '13 at 15:37
1  
A properly indexed table containing six different types of entities will perform just as well as six separate tables (one per entity), so I'm not sure why you think more tables = better performance. Number of tables very rarely has anything to do with anything. –  Aaron Bertrand May 20 '13 at 21:43
show 1 more comment

I would define a people table and a color table as base tables. And then a people-color table that links people with their color. This design is very flexible and extensible. If you have performance issues with queries later on, then you can consider a denormalized version ofthe person-color table to address performance.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.