|
|
Posted by Toby A Inkster on 06/05/07 18:23
Geoff Muldoon wrote:
> But if you must, then ....
This database schema is sound overall, but I'd consider extending it
slightly.
Firstly, some attributes are probably going to be common amongst
all of the different types of product -- things like Name, Price,
and Stock_Quantity.
Other attributes are going to be shared by a few different types of
products, but not all of them. Examples might be Height, Width,
Depth, Colour and so on.
I'd start with a main table of all products including a product ID, a
product type and any of those first set of core attributes.
=================================================
PRODUCTS
-------------------------------------------------
ID Type Name Price Qty
-------------------------------------------------
1 CD The White Album £8.99 12
2 CD Play £7.99 8
3 PNTNG Mona Lisa (NULL) 0
4 CAR Corsa £5999.00 3
5 CAR Previa £7499.99 2
6 BOOK Bleak House £3.99 7
7 BOOK Lucky Jim £4.99 3
8 BOOK Foucault's Pendulum £5.99 2
=================================================
As I said, there might be other "core attributes" in there.
Now, a table listing all our different types of types.
=================================================
TYPES
-----------------------------------------------
Type Type_Desc Type_Desc_Plural
-----------------------------------------------
CD Compact Disc Compact Discs
PNTNG Work of Art Works of Art
CAR Car Cars
BOOK Book Books
SALAD Salad Salads
DVD DVD DVDs
F Fish Fish
=================================================
I've included a "Type_Desc_Plural" column in there which is not entirely
frivolous -- as you can see, the rule of simply adding an 's' to the
singular doesn't always work!
Now we set up a bunch of additional attributes.
=================================================
ATTRIBUTES
-----------------------------------------------
Attribute Attribute_Desc
-----------------------------------------------
COL Colour
H Height
W Width
D Depth
ENGINE Engine size
SPEC Species
BY Maker
=================================================
Now here's an interesting one -- I could have defined "painter", "singer"
and "manufacturer" attributes separately, but I want to allow the
application to realise that these all mean the same thing, so I've just
defined "maker". This allows you to search for all items where the "maker"
is "Joe Bloggs" rather than having to search individually through
paintings, CDs, cars and so forth.
In the next table, we define which attributes are relevant to which type
of product, and also define a more specific Attribute_Desc.
=================================================
ATTRIBUTE_APPLICATION
-----------------------------------------------
Attribute Type Label
-----------------------------------------------
COL CAR Paintwork
H CAR Height
W CAR Width
D CAR Length
ENGINE CAR Engine size
SPEC F Species
COL F (NULL)
COL CD Casing Colour
BY CAR Manufacturer
BY PNTNG Artist
BY CD Singer/Artist
BY DVD Director
BY BOOK Author
=================================================
Finally, we fill in the detailed information:
=================================================
PRODUCT_ATTRIBUTES
-------------------------------------------------
ID Attribute Value
-------------------------------------------------
1 BY The Beatles
1 COL White
2 BY Moby
2 COL Blue
3 BY Leonardo Da Vinci
4 COL Blue
4 BY Vauxhall
6 BY Charles Dickens
7 BY Kingsley Amis
8 BY Umberto Eco
=================================================
So now doing an inner join on products, product_attributes and
attribute_application and searching for blue things, will tell you that
the Corsa's paint work is blue, and Moby's casing is blue.
--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 102 days, 1:31.]
URLs in demiblog
http://tobyinkster.co.uk/blog/2007/05/31/demiblog-urls/
Navigation:
[Reply to this message]
|