4

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.

  • That would depend on the business requirements. If the business wants to track the car that is associated with car seats that are repaired, then you would need the parent/child schema. If it were my business I wouldn't bother with that, but you might ask your instructor. It's not an issue of maintenance and performance, it would be an issue of employees spending time collecting and entering the data, and also additional time spent by developers to build the application. – Tony Hinkle Apr 15 at 10:37
1

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.