AnsweredAssumed Answered

Basic structure for many to many

Question asked by gfsymon on Sep 26, 2017
Latest reply on Sep 27, 2017 by gfsymon

I haven’t setup a new database for many many years and I’d like to have some reassurance that I’m doing so with the best structure. It is very very basic stuff for you guys, but I’d just like reassurance that I’m not about to set off on the wrong foot.  So any help would be most welcome.

 

This database will be used … eventually … for a website structure and it is for a range of ‘modular’ products.  So there are Products which are made up of various Parts depending on customer needs.  You could think of it like a car.

 

Cars are made up from the manufacturer’s base of Parts.  Some Parts will be used in some cars, but not in others.  Certain Parts must be used, whilst others are optional.  For example … a GPS may be an option, but an engine is obligatory.  However, you can choose from different engines according to your wishes.

 

An example might be Honda’s products.  They start their website with Cars / Bikes / Marine / etc.etc. 

Entering the Car category shows you the Model Ranges for Jazz / Civic 5 door / Civic Tourer / CR-V / etc.etc.  Choosing say a Jazz, takes you to a choice of configurations, both optional and obligatory.

 

So, a Model is unique, but can be configured. It forms part of a Range and of a Category.  The Model is made up of many Parts.  Parts are unique and can be used in many Models.

 

Tables:

 

The car metaphor would be like this:

__________________________

Category

Range

Model

Parts (all cars are made from a selection of parts)

__________________________

 

These are the relationships I envisage:

 

Category

CategoryID

 

Range

RangeID

CategoryID

ModelID

 

Model

ModelID

CategoryID

RangeID

PartsID

PartsOptionalID

 

Parts

PartsID

 

 

1/ When viewing Categories, I’d like the Ranges to be shown.

2/ When viewing Ranges, I’d like the Models to be shown.

3/ When viewing Models, I’d like Parts and/or Optional Parts to be shown.  The reason for this is because some Parts will be optional … as in a GPS etc.

4/ Parts will only be viewed when adding/removing parts to the database

 

Most of the relationships are obvious (I think) but the ones I’m uncertain of are Model to Parts.  I can show ALL the parts used in a Model via a PartsID-PartsID relationship and I can also show the ‘optional extras’ via a PartsOptionalID-PartsID relationship.

 

Questions:

 

How best to deal with the ModelID-PartsID and the PartsOptionalID-PartsID relationships.

 

1/ A Part can be used in many Models, so should the PartsID field in the Models Table, simply be a return separated list of PartsIDs?  Same question for the PartsOptionalID relationship … should the PartsOptionalID in the Models table, be a return separated list of PartsIDs?

 

2/ Would it be better to have the PartsOptionalID in the Parts table instead of the Models table, i.e. have the relationship the other way round?  (Same thing for the Optional relationship).

 

3/ Is there a better way of doing this??  

Outcomes