Advice on table structure

[ByNary010101]

I am developing an Inventory tracking system for a Health Care provider; its front end is ASP and back end is SQL Server 2000. I need some suggestions as to how I should organize accessories with main assets, confusing I know but hopefully we can work with that:

Example: When a technician pulls up the application and selects to install a new CPU he/she fills out all of the required information and proceeds to a check out page where he/she can then select other accessories that go with the CPU like keyboard, mouse, monitor, power extender, vga extender, mouse extender, and keyboard extender. However, if a technician selects to install a new laptop he/she should be able to select monitor, keyboard, mouse, wireless cart, valence battery, and cisco wireless card.
Currently I have a one to many relationship between Category (Laptop, Desktop, Printer, Scanner, etc) and Product(IBM X40, IBM T42, Compaq EVO, HP LJ4200, etc)

I hope this makes sense, ultimately I only want the options that belong to a desktop to show up with a desktop, the options that belong to a laptop to show up with a laptop, etc., etc. Thanks a head of time for your input.

 

[mrdenny]

You'll want a table with the Device Types (Monitor, KeyBoard, Mouse, Laptop, Desktop, etc).
Then you'll want a table that has what devices are children on other devices. Laptop children are Monitor, Keyboard, Mouse, docking station, etc.
Then you'll want a table that has the Products. This table relates to the Device Types so that you know that a HP LJ4200 is a printer, etc.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[ByNary010101]

Ok, thanks for that. Working off of this I have another question. Listed below is a general layout of what I think the tables should look like populated with sample data

Table: Category
1 Desktop
2 Laptop
3 Monitor
4 Printer
5 Scanner
6 Keyboard
7 Mouse
8 Ergotron
9 Misc.

Table: Product
1 Compaq EVO Relates: Category 1
2 IBM T20 Relates: Category 2
3 IBM X40 Relates: Category 2
4 NEC 17" LCD Relates: Category 3
5 NEC 17" CRT Relates: Category 3
6 HP LJ4200 Relates: Category 4
7 Zebra Relates: Category 4
8 Intermec Relates: Category 4
9 Low Volume Relates: Category 5
10 Mid Volume Relates: Category 5
11 USB Keyboard Relates: Category 6
12 USB Mouse Relates: Category 7
13 VL Lift Relates: Category 8
14 Mounted Arm Relates: Category 8
15 Power Extender Relates: Category 9
16 VGA Extender Relates: Category 9

Table: Category_Accessory
1 Relates: Category 1 Relates: Category 3
2 Relates: Category 1 Relates: Category 6
3 Relates: Category 1 Relates: Category 7
4 Relates: Category 1 Relates: Category 9
5 Relates: Category 2 Relates: Category 3
6 Relates: Category 2 Relates: Category 6
7 Relates: Category 2 Relates: Category 7
8 Relates: Category 2 Relates: Category 8

Does the above seem like it is the right way to go?? Thanks again.

 

[mrdenny]

Yeah, you've got the idea.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)