CODEWITHSUNDEEP
sql-serversql-server-2008database-design
Kazekage Gaara
Kazekage Gaara

Reputation: 15052

One table with redundant data in one column vs Multiple tables

I have a scenario wherein I have users uploading their custom preferences to the conversion being done by our utility. So, the problem we are facing is

should we keep these custom mappings in one table with an extra column of UID or maybe GUID ,

or

should we create tables on the fly for every user that registers with us and provide him/her a separate table for their custom mappings altogether?

We're more inclined to having just an extra column and one table for the custom mappings, but does the redundant data in that one column have any effect on performance? On the other hand, logic dictates us to normalize our tables and have separate tables. I'm rather confused as to what to do.

Upvotes: 0

Views: 330

Answers (2)

Pete Carter
Pete Carter

Reputation: 2731

I inherited a system where the original developers had chosen to go with the one table per client approach. We are now rewriting the system! From a dev perspective, you would end up with a tangled mess of dynamic SQL and looping. The DBAs will not be happy because they will wake up to an entirely different database every morning, which makes performance tuning and maintenance impossible.

You could have an additional BLOB or XML column in the users table, storing the preferences. This is called the property bag approach, but I would not recommend this either, as it will hamper query performance in many cases.

The best approach in this scenario is to solve the problem with normalisation. A separate table for the properties, joined to the users table with a PK/FK relationship. I would not recommend using a GUID. This GUiD would likely end-up as the PK, meaning you will have a 16-byte Clustered Index, and that will also be carried through to all non-clustered indexes on the table. You would probably also be building a non-cluster index on this in the Users table. Also, you could run the risk of falling into the pitfall of generating these GUIDs with NEW_ID() rather than NEW_SEQUENTIALID() which will lead to massive fragmentation.

If you take the normalisation approach and you have concerns about returning the results across the two tables in a tabular format, then you can simply use a PIVOT operator.

Upvotes: 1

lc.
lc.

Reputation: 116488

Common practice is to use a single table with a user id. If there are commonly reused preferences, you can instead refactor these out into a separate table and reference them in one go, but that is up to you.

Why is the creating one table per user a bad idea? Well, I don't think you want one million tables in your database if you end up with one million users. Doing it this way will also guarantee your queries will be unwieldy at best, often slower than they ought to be, and often dynamically generated and EXECed - usually a last resort option and not necessarily safe.

Upvotes: 4

Related Questions