Skip to main content
added 512 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

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.

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.

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.

Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134

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.