Mysql – Should I declare one or two tables to represent Items and Sub Items

database-designMySQL

I'm facing a dilemma in my database design for an upholstery store.

  • This store cleans carpets, pillows, car seats, car trunks, couches.

  • There are chairs and sofas with fixed and removable seat cushions. Car seats and trunks with different sizes. The same occurs with pillows.

From my point of view all of these things are items. My idea is to create a table like this:

CREATE TABLE Item (
    ItemID …
    Name …
    Description …
    Material … -- (like 'cotton', 'wool', 'rayon', 'silk')
    Length …
    Height …
    Width …
);

The items mentioned earlier have sub items. A car has seats and trunk. A chair can have a fixed or a removable seat, a couch can have pillows and so on.

I've read these posts:

After reading them, I have some questions:

  1. Is it a good idea to include another column called subItem which references a parent item from the same table? Doing that it hurts performance?
  2. Is this a case of a parent/child table?

I'm wondering what approach fits simpler for maintenance and performance.

Best Answer

This looks like a straightforward one-to-many relationship, which as you suggested you can model by setting a foreign key in the child items table referring to the parent items table. As for performance, you won't have any kind of performance hit at all for a small site. Modelling your schema properly is your priority.

Consider renaming the tables too. For example, if the shop offers a cleaning service then you could think of the tables in terms of Services (e.g. car) and Costs (seats, trunk, etc)

Related Question