1

Today my database has the tables in this first image. The problem is that now I need to do stock control and other products will be added too.

devices and sim cards

To support many types of products, first I designed the tables in the next image (design 1). This will be a solution to generic products that don't have any kind of serial number to differentiate they.

After doing some research I found the solution in the next image (design 2). This supports generic and the specific products (like SIM cards and devices). I think that the complexity will increase greatly this way. Also there is a problem that I have FKs in simcards and devices. One great thing about this solution is that will support future products with specific data.

enter image description here

What is the best approach to solve this problem?

  • Do you have a need now for Products_Attributes? If not, why not add it later when you do? – Greg Dec 21 '16 at 5:04
  • Right now I need a way to replace the original tables simcards and devices. Or there is a way to use them with the Design 1? – Marcelo Bruzetti Dec 21 '16 at 12:07
0

Why not add product_type_attributes into design 1? If product_type_attributes is necessary and it is in multiple types then design 2 is much better compared to design 1 because it is more normalized.

  • To replace the original tables I will need something like product_type_attributes, but I will have problem with the FKs. How am I gonna keep the FKs? Like simcards.mobile_operator_id and devices.simcard_id. – Marcelo Bruzetti Dec 21 '16 at 12:04
0

I tend to think you would need an intersection table between Products and Manufacturers. Is it not possible for a product to be provided my more than one manufacturer and/or a manufacturer to supply more than one product?

Other than that, you should adopt whichever design gives you the functionality you need. Why are you even worried about complexity? If increased complexity gives you what you need, then don't question the complexity. Your design must be as complex as it needs to provide a database with all the benefits and features required of it. No more complex than that -- but no less, either.

I've been in many shops where important features were not implemented into the final design in order to reduce the complexity of the design. Yes, more complexity makes maintenance more difficult. But our job is to make our user's jobs easier, not our own.

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.