Page 1 of 1

building correctly a DB

Posted: Thu Nov 18, 2004 4:01 am
by pelegk2
i am building a DB that controls all the pc and there parts in my company
my question is :
if for example i have 2 types of mouse : serial and usb
and 2 types of keyboards- usb and wirless
will i open for each of the mouse/keyboard a seperate table?
or what the correct way to do this?
thnaks i nadvance
peleg

Posted: Thu Nov 18, 2004 4:58 am
by timvw
Type is an attribute of entity MOUSE.
Can a mouse be 2 types at the same time?

No:
mouse(mouse_ID, mousetype);
with mousetype ENUM('USB', 'SERIAL');

Yes:
mouse(mouse_ID, mousetype_ID);
mousetype(mousetype_ID, name);

Posted: Thu Nov 18, 2004 5:16 am
by pelegk2
sorry but i didnt understand execlly which tables to build?
or to build the mouse and the keyboard on same table or not ?!?!?!?

Posted: Thu Nov 18, 2004 5:24 am
by timvw
as a mouse and a keyboard have different attributes, i would make a different table for each.

Posted: Thu Nov 18, 2004 7:06 am
by patrikG
viewtopic.php?t=21400 - the db-section has a link to a excerpt from an O'Reilly book about designing databases.

Posted: Sun Nov 21, 2004 1:09 am
by jl
I would strongly suggest this structure:

Code: Select all

TableTypes    - table for types of item
TypeID  Name
1           Keyboard
2           Mouse
3           Monitor

TableAttributes - table of attributes for each item type
AttributeID      TypeID     Name
1                     1             USB Keyboard
2                    1                PS2 Keyboard
3                     1                Long Wire Keyboard
4                      1              Short Wire Keyboard
5                   2                USB Mouse
6                       2             Serial Mouse
...

TableItems - table of actual items
ItemID         Name
1                  Bobs Keyboard
2                  Jims Logitech Mouse
3                  Spare Keyboard

TableItemAttributes - what item has what attributes
ItemID          AttributeID
1                   1
1                   3
2                   5
This structure allows you to:

1. Define attributes that apply only to certain types of items (e.g. SELECT * FROM TableAttributes where TypeID=1 will get all the possible attributes of keyboards)
2. Store multiple attributes for each item (e.g. SELECT TableAttributes.Name FROM TableItemAttributes , TableAttributes WHERE TableAttributes.AttributeID=TableItemAttributes.AttributesID AND TableItemAttributes.ItemID=1 will return all the attributes assign to ItemID1 - i.e. show you that Bob's Keyboard is a USB Keyboard and Long Wire Keyboard)
3. Not have to monkey around creating tables for every new class of item you want to add, which is not a good idea.
4. And of course easily assign any attribute to any item. E.g. INSERT INTO TableItemAttributes (ItemID AttributeID) VALUES (3,4) will store that the spare keyboard is a Short Wire keyboard)