database-design-inventory-different-attributes

[Burghew]

Hi all,

I am trying to design a database in access 2002. I have multiple category of
items such as computers which may include laptop, server, pc...all of these
items have different attributes, similarly printers with sub categories
deskjet, laser, line, etc also have diff attributes...like that I have
multiple items in my inventory (around 30 items) all with different
attributes. After going through normalisation I guess I need to have
separate tables for each laptop, Server, Computer etc OR have a one to one
relation with item category such as computers 1 to 1 laptop , servers. By
doing that I will have a lot of tables which I guess is not the best way to
do this.
I have to assign them to customers which is related through a many to many
relation...junction table .tbltransactions.... with tblcategory at present
and will be choosing multiple items which might include printers, computers,
routers, switches, etc.
Please suggest if there is a best practise for doing this OR anyway I can
simplify the table design. Thanks to all in advance.
Awaiting eagerly for your response.
--
Regards,
B

[Darryl Kerkeslager]

"Burghew" <no****@sorry.com> wrote

I am trying to design a database in access 2002. I have multiple category
of
items such as computers which may include laptop, server, pc...all of
these
items have different attributes, similarly printers with sub categories
deskjet, laser, line, etc also have diff attributes...like that I have
multiple items in my inventory (around 30 items) all with different
attributes. After going through normalisation I guess I need to have
separate tables for each laptop, Server, Computer etc OR have a one to one
relation with item category such as computers 1 to 1 laptop , servers. By
doing that I will have a lot of tables which I guess is not the best way
to
do this.
I have to assign them to customers which is related through a many to many
relation...junction table .tbltransactions.... with tblcategory at present
and will be choosing multiple items which might include printers,
computers,
routers, switches, etc.
Please suggest if there is a best practise for doing this OR anyway I can
simplify the table design. Thanks to all in advance.

equipment
=======
equipment_id
eq_cat_id (fk)
eq_type_id (fk)
eq_serial_no
eq_model

lu_eq_category
=========
eq_category_id
eq_cat_desc {printer, computer, monitor, speakers}

lu_eq_type
=======
eq_type_id
eq_cat_id (fk)
eq_type_desc { ink, laser, laptop, server, LCD, CRT }
On a form, first choose an eq_cat_id from a listbox or combox that uses
lu_category_id as the lookup table.
Then requery the second listbox/combobox, which has as its rowsource a
query like "SELECT eq_type_id, eq_type_des FROM lu_eq_type WHERE eq_cat_id="
& Me.cboCategory"

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Phil Stanton]

Just a suggestion because things have changed since I designed a similar Db
where we sold a client a system, and networks were uncommon.
If I were doing it now, I think I would have
A TblEquipment:-EqipmentID, TypeID(FK), Serial,
TblTypes:-TypeID, Type e.g. Computer, Printer, Router
TblCategories:-CategoryID, Category, TypeID e.g. LaserJet, Laptop
TblJoinEqipmentCategory:-EqipmentID, CategoryID but a check that the
TypeID is consistent, i.e you can have a LaserJet printer, but you can't
have a LaserJet computer.
This should allow you to build a piece of equipment and show it's
categories.
Then as you surmised you have a TblCustomer, CustomerID, Customername etc
and a JoinCustomerEquipment with date, Price etc

Idle thoughts

Phil

"Burghew" <no****@sorry.com> wrote in message
news:d0*********@news-dxb.emirates.net.ae...

Hi all,

I am trying to design a database in access 2002. I have multiple category
of
items such as computers which may include laptop, server, pc...all of
these
items have different attributes, similarly printers with sub categories
deskjet, laser, line, etc also have diff attributes...like that I have
multiple items in my inventory (around 30 items) all with different
attributes. After going through normalisation I guess I need to have
separate tables for each laptop, Server, Computer etc OR have a one to one
relation with item category such as computers 1 to 1 laptop , servers. By
doing that I will have a lot of tables which I guess is not the best way
to
do this.
I have to assign them to customers which is related through a many to many
relation...junction table .tbltransactions.... with tblcategory at present
and will be choosing multiple items which might include printers,
computers,
routers, switches, etc.
Please suggest if there is a best practise for doing this OR anyway I can
simplify the table design. Thanks to all in advance.
Awaiting eagerly for your response.
--
Regards,
B

[pietlinden]

Read Rebecca Riordan's article at Access web on subclassing in Access.
One of the reasons VB.NET *should* be better - it can do real
subclassing and all the inheritance and all that. You might want to
look at Peter Vogel's book too (Object and component handbook).

http://www.mvps.org/access/tables/tbl0013.htm

[Albert D. Kallal]

<pi********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Read Rebecca Riordan's article at Access web on subclassing in Access.
One of the reasons VB.NET *should* be better - it can do real
subclassing and all the inheritance and all that. You might want to
look at Peter Vogel's book too (Object and component handbook).

Yes, but none of the object stuff works with sql server...so what good does
that do? If you use ms-access, or you use c#.net, they both still have to
use sql statements to grab the data from JET, or sql-server. The issues of
using .net will NOT effect a correctly formed and normalized database (and,
I not suggesting that you are saying otherwise, but the issues of a OO
programming languages has little, or nothing to do with the fact that using
JET (ms-access), Oracle, or ms-sql requires a good normalized design here,
and the approach here does not differ).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal