Friday, March 30, 2012

Help in database design

I am designing an inventory database in which I need some help, I have the following entities:

Items
Notebook
Wireless Card
ADSL Modem
etc...
Models
Acer centrino 1.6
Acer centrino 1.73
3COM 4x1
25 hours Wi-fi access
50 hours Wi-fi access
etc...

Packages
Package A:
Acer centrino 1.6
3COM 4x1
25 hours Wi-fi access
Package B:
Acer centrino 1.73
3COM 4x1
50 hours Wi-fi access
I made a table for the items having the following fields: Item_ID, Item_Name
and another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)
up to this point is this correct?
About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes them
I don't know how to relate the packages and the models table.
Any recommendations for a proper design for those entities?

I would use the relational data model, and design it as follows. I really can't show you how the Items table is to be implemented in this schema as I haven't seen how you intend to use that table yet.

Table: Items
Columns: ItemID, ItemDescription

Table: Models
Columns: ModelID, ModelDescription

Table: Packages
Columns: PackageID, PackageDescription

Table: PackageModels
Columns: PackageID, ModelID

|||Also look like a relationship (1-M) between item and model although the naming seems strange.|||

Thanks for your replies,but they were a little bit late, I already finished the projectSmile [:)]

Here is the design I used, maybe it would be useful for anyone who might face something like that.

Items Table

Item_ID, Item_Name

Models Table

Model_ID, Model_Name, ItemType(FK to Items table,i.e. this model is of which item type)

Packages Table

Package_ID, Package_Name

Package_ItemModel

Package_ID, Item_ID, Model_ID

|||I'm suprised that Model isn't independant of item.|||

Why?

I need to specify whether hp nx6110 is a notebook,router or card and so on for other types.

|||argh, sorry the naming has confused me yet again.

No comments:

Post a Comment