database design (Advice)

[deletedUser2352352]

Hello all

I was wondering if any of you great people could help me with my database design.

link to image below
http://www.our-atelier.co.uk/uploads/database_design.jpg

All advice would be greatly accepted as i'm still a complete novice at this.

points to remember

1. each item can have more then one upholstery choice
2 each item can belong to more then one product listing (eg a item can be a table and a chair)

3. each item can have more then one frame option ( note each frame can have different colour options confused on this so advice on that would be great)

Hope this makes sense.

Thanks in advance.

:)

[bwh2]

you're on the right track. the only thing i would change is taking colour_id out of cms_item_frame. make a cms_frame_colour table with frame_id and colour_id as FKs.

^ my assumption is that the available frame colors does not depend on which item is selected. that is, i view the user as having the ability to choose 1) an item, then 2) a frame, then 3) a color. not A) an item, then B) a frame/color combination. if my assumption is incorrect, then i would keep things how you have it.

[Pasquale]

Oh wow I haven't done this for a WHILE. I might have a crack when I am less inclined to clamp my eyelids together and catch some z's

[deletedUser2352352]

yes bwh2 thats correct.

I did that in the first place but didn't think that was right.

Any other advice on this. Is it efficient enough or can it be tweaked?

[deletedUser2352352]

New version

http://www.our-atelier.co.uk/uploads/database_design_two.jpg

[bwh2]

you have cms_frame_colour linking to the wrong table. it should link to cms_frame, not cms_item_frame. i think you're good to go in terms of the db design.

[deletedUser2352352]

cheers.

I do have a few more things to put in. But thanks for that.

Is it ok if i keep posting them now and again to see if i'm on the right track.

[deletedUser2352352]

right i have another question

A item maybe complimented by another item how would that be done.

eg:
cms_compliment
fk item_id // contains item_id
fk comp_item_id contains other items_id.

Or would it be better to place the compliment_id in the item table?

[bwh2]

that's exactly what i would do. and for the SQL, i would do something like in this thread: http://kirupa.com/forum/showthread.php?t=253054

[deletedUser2352352]

sorry which one?

:)

[bwh2]

well, if you make this table,:

cms_compliment
fk item_id // contains item_id
fk comp_item_id contains other items_id

you will need the relationships to work both ways. that is, if you have item_id = 2 and comp_item_id = 1, when you query that table for item_id=1, you should also be querying for comp_item_id = 1. at least that's how i view it. in other words, if 1 complements 2, then 2 complements 1. but i suppose you can have a setup where the complement is one directional.

the query i showed basically does the dual directions.

btw, just as a sidenote, "compliment" refers to praise, while "complement" refers to mutual completion. so you want "complement". but i'm a semantic prick sometimes.

[deletedUser2352352]

ok so if i had a limit of 3 compliment items id set my table up like this

cms_compliment
fk item_id
fk comp_one
fk comp_two
fk comp_three

thats seems fair to me.

But what sort of query would i run then? Also is it efficient to do it like this?

[bwh2]

no, i would still set up the table like you had it before.


cms_complement
-------------------
item_id
comp_item_id

then query:


SELECT c.*
FROM cms_item a
LEFT OUTER JOIN cms_complement b
ON a.item_id = b.item_id
OR a.item_id = b.comp_item_id
INNER JOIN cms_item c
ON b.item_1 = c.item_id
OR b.comp_item_id = c.item_id
WHERE a.item_id = 5
AND c.item_id != 5
LIMIT 3

[deletedUser2352352]

fantastic cheers for that.

I've just been doing some logic and it makes sense.

:)