Delimited values in a column value

database-design

I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like to go with 1NF here and add new table to keep the relationship. The number of rows linked from T2 is around 10 at maximum. The PK of T2 is a bigint. So doing this will require me to either join which will be not really good because of dplicates that will come from T1. Second is to do a seperate query from application to get the T2 PKs. Now if I go with option 2 every thing is nice but I require an additional query. Then I though why I can't keep the T2 PKs in a T1 column delimited. This will avoid a query that is require just to find the T2 ids. Well this is feasible since there never will be a requirement to query T1 values by T2 PKs. Any other good solution for this? Or should still stick with 1NF to solve this.

Best Answer

If you're designing an OLTP database, you should really design it in third normal form initially. If, once you've done that, you find that you can make measurable performance improvements by denormalizing something and there is no other way to get those improvements, it makes sense to denormalize selectively. It does not make sense, however, to denormalize to first normal form proactively.

I don't really understand why you expect that adding a mapping table would be problematic. Yes, you'll need to join to it. But that's what a relational database was built to do-- it joins tables very very efficiently. Plus, you won't have to constantly parse the delimited string to figure out the T2 keys. And you will be able to create a foreign key that ensures that the T2 keys in the mapping table are valid and that you don't have keys in your delimited string that don't map to a valid row in T2. I don't really see the downside.

If you have a separate mapping table and you want to return a single row of data to the client with the T1 data and the T2 keys (which seems awfully suspicious-- the T2 keys are only useful if you are going to subsequently use them to query T2 in which case you almost always want to just let the database join to T2 in the initial query) you can always pivot the data in the mapping table to return a delimited string of T2 keys. I certainly wouldn't recommend that but it can be done.

Related Question