08-09-14, 20:18 #1Registered User
- Join Date
- Aug 2014
Unanswered: simple? Database design help request
I've been assign by the powers that be to create a web page that show our products. I working knowledge for website creation but am severely lacking in the db design area.
I feel if I can get the design correct I will have 75% of what I need accomplish. The db needs to go something like this.
Our products consists of 50 door styles, 7 wood species and 80 stains.
a portion on the doors can only built on some of the wood species.
a portion of the wood species can only be finished with some of the stain colors.
The first page would show all the doors in a grid. when the users click on a door it will open up into a new page showing only that door and the available
wood species for that door. under the available wood species the stains will show (the first available wood specie's stains would show under the wood specie group)
when the user clicks on the any item in the wood species group the corresponding stains would show.
what I feel I need so far is
tables doors wood stains
id id id
name doorId woodId
picURL name name
Is there something I am missing? Shold I just put 7 boolans under the doors table for the wood?
should I make a child db (I don't know if there is such a thing but some research showed me such a thing, but I could really grasp it properly.)
I would also like to have the option having a page that just shows the wood species and the corresponding stains.
In the future I plan on putting an image of the door with a wood and stain skin showing a likeness of the door....that is a ways away but from what I read being a part of the db design now would be best.
It seems like this should be simple design but I cannot get my head around how it can be done.
The rest of the site is nearly complete and I have been toiling over this in the background the whole time now I come to you hat in hand for assistance.
08-17-14, 08:05 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
so if it were me id be looking at something like
a table for:-
...B) material types
then a table that defines a raltionship between two parent tables, a so called intersection table...
...D) what doors can be made from what material types
...E) what finishes can be applied to what material types
...F) then a further table,another intersection table, (your actual product table) which defines what door styles are available in what finishes in what material types.
seems a long winded way of doing this, and it is, except that its a way of being flexible in the future. say your compnay decides to add a new wood variety, or a new finish (or 10 or 20) this way round there are no deverlopmentg changes required, the comapny enters the new data, defiens the product matrix themselves and is in control of that data themselves.
so a ferinstance
A 'colonial', 'flush', 'half glass', 'full glass'
B 'Mahogany', 'Iroko', 'cedar', 'uPVC'
C 'gloss varnish', 'matt varnish', 'oiled', 'polished'
D colonial : Mahogany, colonial : pine...
E uPVC : polished, Mahogany : Oiled, Pine : gloss varnish
F Colonial : uPVC : polished, 4 panel : pine : unfinished
you woudl use the key NOT the text in the intersection tables
G) you'd likely have a another product tbale underneath this which defned products in specific sizes. the size would refer to a parent table.. the reason for this is it reduces the risk of confusion (eg on person may quote a door as 6' x 3', someone else may quote that a 72" x 36", or in metric... as millimetres or metres.
you'd store probably at the table F level stuff specific to that door product ie is a fire door, is suitable for external/internal and so on
for a customer visiting a website they can specify significant elements to them. Id expect first off would tbe the door size/opening, and afteer that who knows the style or material.
Level G is where you'd know about stock.I'd rather be riding on the Tiger 800 or the Norton