Arguments for storing binary asociations in a single field vs. in a mapping table?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.