1

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.

  • 9
    There is no OptionB, only A. Storing data as comma delimited values in databases is ground for immediate termination. – Remus Rusanu Oct 21 '14 at 6:48
  • Can you provide the exact queries you are going to use to get to your data? Also, how are you going to update this? Is the performance of updates also relevant? – Jens Schauder Oct 21 '14 at 10:13
4

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.

7

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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