Which one is better performance and (approach): store as rows or as a varchar column

Question

OptionA:

TableA:

• TableA_ID (PK,int)
• ..

TableB:

• TableB_ID (PK,int)
• ..

TableC:

• TableC_ID (PK,int)
• ..

TableD:

• TableA_ID (FK, int)
• TableB_ID (FK, int)
• TableC_ID (FK, int) (Nullable)

Composite Key on (TableA_ID,TableB_ID,TableC_ID)

I am thinking to design TableD as the following (OptionB)

TableD:

• TableA_ID (FK, int)
• TableB_ID (FK, int)
• TableC_ID (varchar(max) ) (Nullable) and remove the composite key. So, TableC_ID column will have something like these: {1,2,3,4,....}

My application will need to use TableD as search a value in TableB_ID and/or TableC_ID column by giving the key for value in TableA_ID.

I am just wondering which option will be better performance.

Answer 1

Option B is a bad choice and to answer you question on performace.

Perfomance wise option A is better choice.

Option A will have simple query where as option B need to run complex sql query.

Option A offers better indexing options which in turn offers better performance.

Option A is neat and clean design.

Answer 2

Option B is the worst choice: Why Because by keeping values in CSV format in a single column, you are violating first NF http://en.wikipedia.org/wiki/First_normal_form.

Most critical drawback of violation of first NF is complex join conditions. You have to apply join using LIKE operators and worst wildcard options or using other string functions.

Secondly RDBMS will not be able to create correct data distribution statistics and ultimately query optimizer can't select efficient execution plan.

Answer 3

Option A is much better as compare to Option B. If you use Option B then the complexity of the query will far more then as compared to Option A where you can get your result by using JOIN statements.