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)