0

I'm confused about how to structure a database when the items I'm adding are similar but not identical types. Consider adding toys to a Toys database. I have:

  • Cars
  • Dolls
  • Balls

All are toys, but a car needs to record whether it is a remote control or manual car, A Barbie needs to record whether she has accessories, and a ball needs to record what type it is. I feel I have two choices:

  1. A toys table that stores all toys, but a column called 'Type' is only applicable to balls. The column 'Accessories' is only applicable to dolls. 'RemoteControl' is only applicable to vehicles. So I have lots of columns not being used and filled up with null values. Or columns with nondescript names, like Value1, Value2.

  2. I have a table for each type of toy, such as a 'Vehicles' table and a 'Dolls' table. I have another table that stores the toy ID and type ID, which I use to identify which table I need to query to get its full details. The issue now is that when I decide to add Lego and fidget spinners to my database, I need to add two new tables.

I'm really confused about the best practice for this kind of situation. Any advice would be greatly appreciated.

Rick James
  • 135,179
  • 13
  • 127
  • 222
user7043436
  • 286
  • 1
  • 2
  • 11
  • This question should *not* have been closed as opinion-based. Representing inheritance in a relational database is a real problem with real answers. @user7043436 Never use the phrase “best practice” as some people here are allergic to such wording, and vote to close in a knee-jerk reaction without considering the context and merits of the question, thereby abusing the purpose of “opinion-based” closing. – Basil Bourque Jun 27 '21 at 16:59
  • 1
    Duplicate of: [*How can you represent inheritance in a database?*](https://stackoverflow.com/q/3579079/642706) and [*How do you effectively model inheritance in a database?*](https://stackoverflow.com/q/190296/642706). There may be related Questions on sister site https://dba.stackexchange.com/ – Basil Bourque Jun 27 '21 at 17:02
  • @BasilBourque Thanks for the advice. This is a real shame. I don't think it's opinion based. I'm looking for expertise. – user7043436 Jun 27 '21 at 17:07
  • 1
    Follow the EAV tag that I added. – Rick James Jun 27 '21 at 18:34
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jul 12 '21 at 17:40

0 Answers0