So, let’s say I have 5 items, A, B, C, D and E. Item A comes in sizes 1 and 2, item B comes in sizes 2 and 3, C comes in 1 and 3, D comes in 1 and E comes in 3. Now, I am considering 2 table options, as follow:
Table 1
| Name | Size |
|---|---|
| A | 1 |
| A | 2 |
| B | 2 |
| B | 3 |
| C | 1 |
| C | 3 |
| D | 1 |
| E | 3 |
Another option is Table 2, as follows:
| Name |
|---|
| A1 |
| A2 |
| B2 |
| B3 |
| C1 |
| C3 |
| D1 |
| E3 |
Now, which of these 2 tables is actually a better option? What are the advantages and disadvantages (if any) of each of the 2 tables above? One thing that I can think of is that, if I use table 1, I can easily extract all items by size, no matter what item I want. So, for instance, if I want to analyze this month’s sales of items of size 1, it’s easy to do it with Table 1. I can’t seem to see the same advantage if I use table 2. What do you guys think? Please kindly enlighten me on this matter. Thank you in advance for your kind assistance, everyone. Cheers! 🙂
Advertisement
Answer
I don’t even understand why you have the second table option – what purpose does it have or how does it help you? Plain and simple you have a one to many relationship. That is an item comes in 1 or more different sizes. You just saying that sentence should scream ONLY option 1. Option 2 will make your life a living hell because you are going against normalization guidelines by taking 2 datatypes into 1, and it has no real benefit.
Option 1 says I have an item and it can have one or more sizes associated with it.
Item Size A 1 A 2 A 3 B 1 C 1 C 2
Then you can do simple queries like give me all items that have more then 1 size. Give me any item that only has 1 size. Give me all the sizes of item with item id A, etc.