Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

When do you draw the line between relational tables, and fields holding multiple values of a particular type. Let me illustrate in an example below where a PokeMaster can be associated to multiple pokemon of unknown number.

TablePokeMasters
    PokeMasterId
    PokeMasterName

TablePokeMastersToPokemon <- Side question, what is a good naming convention for a linking table?
    PokeMasterId
    PokemonId

TablePokemon
    LinkId
    PokemonId
    PokemonName

Vs

Table PokeMasters
    PokeMasterId
    PokeMasterName
    PokeMasterPokemon (ex: Picachu, Charizard, Blastoise) <- parse this in code

Is the second method ever preferred over the first one where there is an unknown number of relationships from one table to another? The only value I can see in the second method is a smaller number of tables, and it is easier to see the relationships right inside of one table, instead of building queries to see the relationships.

share|improve this question
    
Why is this tagged with "C#"? –  FrustratedWithFormsDesigner Jun 7 '11 at 19:24
    
To your side question, I use the same name strategy - [SomethingToSomething]. –  user8685 Jun 7 '11 at 19:27
2  
If you always prefix each table name with "Table" which isn't a part of your domain model, then I encourage you to rethink this naming strategy. –  user8685 Jun 7 '11 at 19:28
    
To clarify, in the 2nd method, are you actually storing that text as comma seperated data in a single field? Or are you implying there are separate rows, once for each? –  GrandmasterB Jun 7 '11 at 19:39
1  
With an unknown but potentially large number of relationships ("gotta catch them all"), the second technique breaks down fairly fast. The field has to be able to grow almost indefinitely (particularly if Nintendo releases games beyond White and Black), and doing anything intelligent with it is going to be difficult. –  David Thornley Jun 7 '11 at 19:41
show 2 more comments

6 Answers

up vote 4 down vote accepted

Based on the clarification that we're talking about storing data as comma seperated text in a table - dont. It makes it harder to search, makes it more difficult to edit the data, and makes the app harder to extend down the road.

The only time doing that would be appropriate is if you are denormalizing the database to squeak out every last bit of performance, and you are storing both the individual pieces of data in their own table, and a separate column (kept in sync) with formatted text data. You would do that to simplify queries to speed them up on the retrieval at the expense of slowing things down on insert & updates.

share|improve this answer
    
I completely agree with you. The point of this thread is for me to gain more confidence in my belief in relational database design. It's something I want to push inside of my organization. –  sooprise Jun 7 '11 at 20:36
    
+1: Marshalling complex data into a single SQL column is a huge anti-pattern. What happens when you need to find all the masters of the Megalith ? –  kevin cline Jun 7 '11 at 23:13
    
and a column as a csv almost completely defeats the purpose of having a relational database in the first place –  WuHoUnited Jun 7 '11 at 23:31
add comment

TablePokeMastersToPokemon <- Side question, what is a good naming convention for a linking table?

These are called associations or association tables.

Also, no table name should ever begin with Table. That's as silly as starting every variable name with "Variable"

The only value I can see in the second method is a smaller number of tables, and it is easier to see the relationships right inside of one table, instead of building queries to see the relationships.

That's a false economy. Tables (and associate storage) are not what you're trying to optimize.

The second example does away with separate primary keys. This means that a simple renaming (to correct a misspelling of "PikaChu", for example) no longer touches one row, but now touches numerous rows in the association table.

share|improve this answer
    
I agree that that is a false economy, what can I say in response to someone who suggests using the second method in order to reduce the number of tables? –  sooprise Jun 7 '11 at 19:37
4  
You say that reducing the number of tables does not increase the value or the quality of the data scheme, while eliminating integrity checks through foreign key associations represents a tangible and immediate risk of data corruption. –  user8685 Jun 7 '11 at 19:41
    
You say "a simple renaming (to correct a misspelling of "PikaChu", for example) no longer touches one row, but now touches numerous rows in the bad-idea table." –  S.Lott Jun 7 '11 at 19:57
1  
@sooprise: If their chief concern, above all else, is "too many tables!!!" then they should probably not be designing databases. But they probably already are... :( –  FrustratedWithFormsDesigner Jun 7 '11 at 20:13
    
...and that may incur serious performance penalties. –  FrustratedWithFormsDesigner Jun 7 '11 at 20:36
add comment

The second method is less robust but allows for the best performance. Most of the time you'll want to keep your schema as robust as possible because data lives much longer than your code.

My Association Table Convention

I would name your relation table like this.

{One}{Many}

Or for your specific example

PokeMaster
    Id
    Name

Pokemon
    Id
    Name

PokeMasterPokemon
    PokeMasterId
    PokemonId

The prefix of Table really doesn't help very much here.

share|improve this answer
    
About the prefix, noted, I will change my naming conventions from now on, it seems other members agree with this point also (the more I think of it the more it makes sense too). –  sooprise Jun 7 '11 at 19:39
add comment

I certainly cannot see many real benefits from the second method when it comes to maintaining your data. You use relational table structures to help in many ways, including making life MUCH easier when you perform all of your CRUD operations (e.g. give me a quick and easy way to update "Charizard" in your sub data above - there isn't).

Likewise, you would have to really jump through hoops to report on that data, and if ever down the road it will need to be put into a data warehouse of some type, making a dimension table from that would be a nightmare.

share|improve this answer
add comment

If you have a low number of records the performance hit of what you are suggesting is negligable. However when you hit several thousand records and want to query on the pokemon you will see serious impact. Not to mention using a varchar comma delimited list to store the data will run into a limit of the data you can store. With a relational table you have a practically unlimited ability to link the pokemon.

share|improve this answer
add comment

Sometimes the second form with multiple values is useful:

  • When there isn't a respective value in the other table any longer (foreign key integrity is not available)

  • When there isn't a matching value yet (perhaps the business rules allow a user to map to something which doesn't exist yet - happens)

  • When there is a need to map to a larger pool of values not fully contained in the mapping table

All of those are rather special cases but they are real.

share|improve this answer
1  
These are interesting special cases, thanks for posting, I will remember these. –  sooprise Jun 7 '11 at 19:38
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.