Database Design Advice

[Diana]

Hi,

I am creating a database for server parts. My company has
8 servers onsite at one of our largest customers for the
sole purpose of using the parts in these servers to repair
any of our customers servers. Of the 8, three of them are
the same model.

Creating one table and repeating the model and serial
number next to every part seems redundent. However,
creating 8 individual tables, doesn't sound right.

The fields I need are simply Model, PartNo, and
PartDesc. ..

Any suggestions are appreciated,
Thank you.

 

[tina]

suggest 3 tables:

tblServers
SerialNumber (should be able to use this as the primary key)
ServerModel
(list each server one time)

tblParts
PartID (primary key. i don't know much about parts; if they all have a
serial number, it could be used as the primary key, same as for servers)
PartName

tblServerParts (linking table between tblServers and tblParts)
SerialNumber (combination primary key)
PartID (combination primary key)

list all 8 servers in the server table. list all the parts of a server that
you want to track, in the parts table. Note: at this point you are not
linking parts to a specific server. just list names of parts - hard drive,
CDROM, etc, etc, without any specific identifiers.
use a form to list the specific parts for each server, in tblServerParts -
as many records as you need for each server. use a combo box for each field,
with the RowSource set to the appropriate table - tblServers or tblParts.
this gives you basic tracking of the server parts, for the 8 servers you
describe. from there, you can expand/add to the tables to track what
specific part is used where, if that is your intention.

hth

 

[Guest]

Thanks Tina, very helpful.
If you don't mind, I am curious of another aspect of this
database that you may be able to enlighten me on. Each
server part, which I omitted to mention earlier, also have
a quantity. I want the technicians to be able to remove
parts when used and replenish when replacement is
received. My goal is to have the quantity automatically
deplete/replenish through some type of signout/signin
form. After quite a bit of research and advice, I
purchased an ASP book to assist me to create this
function. I haven't completed reading this book as of
yet, but, now I am curious if such a function already
exists within Access. Maybe I am making this more
difficult than it is...

 

[tina]

hmmm, i haven't done a lot with inventory tracking, so perhaps this is a
little out of my depth, as far as giving you solid advice. by "sign in/sign
out", i assume you mean having the technicians logging number of parts
out/in as they use/return them. for simple tracking, my first thought would
be a table that tracks part in/part out, quantity, date. current available
parts would be a calculation, "all the pluses, minus all the minuses".
for instance,
tblPartsInventory
PartInvenID (primary key, autonumber)
PartID (foreign key from table parts)
QuantityAdded
QuantityDeleted
DateEntered (never use the word "Date" by itself as a field name)

so each record would have a value in either the Added field *or* in the
Deleted field. you could get a current count in a query, form or report with
something like
Sum(QuantityAdded) - Sum(QuantityDeleted)

pretty crude i think, again due to my lack of practical experience with this
issue. you might do better to ask this question as a *new post here in this
newsgroup* - probably one of the MVPs would have better suggestions for you;
if it can be done in Access, one or more of them will know how to do it.