This question is in the context of adding a new field or fields to a table. – Specifically in my following example, whether equipment's column to reference make-model.ID should be one field or two.
Say, there exists a table named make-model that stores dependent information.
| ID | Name | Parent |
|---|---|---|
| 1 | Acme | NULL |
| 2 | 1000 | 1 |
| 3 | Anvil | 1 |
| 4 | Omni | NULL |
| 5 | 1000 | 4 |
| 6 | Photocell | 4 |
This table is meant to represent a category/subcategory relationship that can be represented as:
Acme
- 1000
- Anvil
Omni
- 1000
- Photocell
Notice that there are two 1000 models, but they belong to different makes, this is why the ID column is the key.
There is also a table named equipment that stores information about the equipment, including the make/model.
Based on user input, the breed is set and could be specific to Anvil or stop at Acme. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)
In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (Acme) and one that would show the more specific level (Anvil).
As far as actual columns on the Equipment table, what is the benefit of one column storing the most specific id given (3 if Anvil, 1 if Acme) or two columns, one for each level?
So, for example
Table A:
| ID | Name | Make-Model |
|---|---|---|
| 1 | Max | 2 |
| 2 | Spot | 3 |
| 3 | Fluffy | 4 |
Vs. Table B:
| ID | Name | Make | Model |
|---|---|---|---|
| 1 | Max | 1 | 2 |
| 2 | Spot | 1 | 3 |
| 3 | Fluffy | 4 | NULL |
My question is about the benefit of Table A's design vs. Table B's design
As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it – so the information is accessible either way.
The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.
Best Answer
Based on your update I understand your question better now. Table A is the more scalable, maintainable, and normalized solution that will lead to less data redundancy.
You can always join to the Breeds table to get the Parent Breed when needed (which may not always be the case). And if you ever have a reason to add a 3rd level (subcategory of a subcategory) then you don't need to change your schema at all to support this when going with Table A (and the Breeds Table) schema (as opposed to Table B where you'd need to add a 3rd column for Level 3, etc which defeats most of the purpose of the Breeds table). (E.g. You can get any level of relationships with a recursive query when using Table A schema.)
Based on my understanding of your schema, I think your Table structure is fine for the Parent-Child relationship as is. I just find the naming slightly confusing since Dog vs Lab isn't exactly the same thing, so to see them objectified the same way as "Breed" feels a little off.
Alternatively, as someone who is seeing the Table for the first time, I'd expect this table to have things like "Shepard" as the Parent under the Breed column and "German" and "Australian" as the children, since they're all breeds. And things like Dog and Cat would be in a different field called AnimalType. But that's probably more my subjective opinion, I think for your goal this Table design makes sense, and maybe just thinking about a slightly different name for the "Breed" column could be a small change.