Help with initial design

[steveh]

My company is in need of a database. We receive equipment from customers for repairs. Each piece of equipment has a serial number in 99.9% of the cases. Occasionally we receive a piece that has no serial number. We need to be able to track the service records in case we receive a piece of equipment back that is under warranty. We would also like to use the database for some level of inventory control. This is my first shot at a db and I need some advice from experts. I have read some books and looked at tutorials. I understand from what I have read that the design should be well thought out and that is what I am attempting to do here. Please send any comments or suggestions on the following. Thanks!

Table

TblCustome
CustomerID(PK
Last nam
First nam

TblProduct
ProductID(PK?
Serial Number(PK?
Mak
Mode

TblServiceRecord
SRID(PK
Dat
type of servic
parts use

TblInventor
InventoryID(PK
Part Numbe
Part Descriptio
Supplie
Parts in stoc
reorder leve

The following tables are only necessary if I am understanding relationships correctl

TblCustomerProductLin
CustProdID(PK
CustomerID(FK
ProductID(FK
SRID(FK

TblSRInventoryLin
SRInvID(PK
SRID(FK
InventoryID(FK

Relationship
Each customer can have many products and service records
TblCustomer Many-to-many relationship to TblProducts and TblServiceRecords via TblCustomerLin

Each product can have many service records but only one custome
TblProducts One-to-many relationship to TblServiceRecord

Since TblCustomer already has many-to-many relationship with TblProducts what happens here since each product can have only one customer

Each service record can have one product, one customer and many parts(inventory
TblServiceRecords many-to-many relationship to TblInventory via TblSRInventoryLin

Since TblCustomer already has many-to-many relationship with TblServiceRecords what happens here since each service record can have only one customer

Since TblProducts already has many-to-many relationship with TblServiceRecords what happens here since each service record can have only one product

-----------------------------------------------------------------------------------------------------------------------------
need to be able to query/lookup service records based on customer or serial number(not sure which is best
If by customer would have to select serial number then service dat

If by serial number would have to select service date(seems like the better choice
------------------------------------------------------------------------------------------------------------------------------
Does serial number belong in the products table or the service records table
Same Make and Model may apply to many different Serial number

Do I need SRID in TblServiceRecords for PK or is Serial Number O
Serial number would always be unique. May occasionally have product with no serial number(probably answers my question. Would need SRID

 

[GVaught]

You may want to take a look at the built-in wizard database for Service
Management or Inventory Control. One or both may contain the fields you need
to track your information. Then add those not covered and/or remove the
fields not needed. You could also import fields from one db into the other.
Just a thought. May save some development time and at least you know table
relationships will already be set correctly.

My company is in need of a database. We receive equipment from customers

for repairs. Each piece of equipment has a serial number in 99.9% of the
cases. Occasionally we receive a piece that has no serial number. We need to
be able to track the service records in case we receive a piece of equipment
back that is under warranty. We would also like to use the database for some
level of inventory control. This is my first shot at a db and I need some
advice from experts. I have read some books and looked at tutorials. I
understand from what I have read that the design should be well thought out
and that is what I am attempting to do here. Please send any comments or
suggestions on the following. Thanks!

Tables

TblCustomer
CustomerID(PK)
Last name
First name

TblProducts
ProductID(PK?)
Serial Number(PK?)
Make
Model

TblServiceRecords
SRID(PK)
Date
type of service
parts used

TblInventory
InventoryID(PK)
Part Number
Part Description
Supplier
Parts in stock
reorder level

The following tables are only necessary if I am understanding relationships correctly

TblCustomerProductLink
CustProdID(PK0
CustomerID(FK)
ProductID(FK)
SRID(FK)

TblSRInventoryLink
SRInvID(PK)
SRID(FK)
InventoryID(FK)

Relationships
Each customer can have many products and service records.
TblCustomer Many-to-many relationship to TblProducts and TblServiceRecords via TblCustomerLink

Each product can have many service records but only one customer
TblProducts One-to-many relationship to TblServiceRecords

Since TblCustomer already has many-to-many relationship with TblProducts

what happens here since each product can have only one customer?

Each service record can have one product, one customer and many parts(inventory)
TblServiceRecords many-to-many relationship to TblInventory via TblSRInventoryLink

Since TblCustomer already has many-to-many relationship with

TblServiceRecords what happens here since each service record can have only
one customer?

Since TblProducts already has many-to-many relationship with

TblServiceRecords what happens here since each service record can have only
one product?

-------------------------------------------------------------------------- ----------------------------------------------------
need to be able to query/lookup service records based on customer or

serial number(not sure which is best)

If by customer would have to select serial number then service date

If by serial number would have to select service date(seems like the better choice)
-------------------------------------------------------------------------- -----------------------------------------------------
Does serial number belong in the products table or the service records table?
Same Make and Model may apply to many different Serial numbers

Do I need SRID in TblServiceRecords for PK or is Serial Number OK
Serial number would always be unique. May occasionally have product with

no serial number(probably answers my question. Would need SRID)

 

[Pavel Romashkin]

tblCustomer may benefit from adding contact information, like address,
phone # etc. If you have complex contact information, this may need to
be further split in 2 other tables (unlikely).
I think it is best to use Autonumber PK for tblProducts, not the SN
field. It is possible that two completely unrelated products might have
the same SN.
In tblServiceRecords, all is Ok except Parts Used field is not going to
work at all
TblSRInventoryLink needs to have a composite PK consisting of SRID and
InventoryID, and CustomerID, too.
tblInventory is a little too simplified, but this may be what's you
need. I think, inventory keeping is a bit more involved. Ideally, you'd
want tblInventoryItems and tblInventoryTransactions, where you'd enter
inventory aquisition details. This may come in handy if you have to use
LIFO for your inventory accounting.

Each customer can have many products and service records.
TblCustomer Many-to-many relationship to TblProducts and TblServiceRecords via
TblCustomerLink

No, no relationship with Products. It will relate to Products via
TblCustomerProductLink. But there has to be CustomerID FK in
tblServiceRecords. However, you can add CustomerID FK to Products and
get rid of TblCustomerProductLink.

Each product can have many service records but only one customer
TblProducts One-to-many relationship to TblServiceRecords
Right!

Since TblCustomer already has many-to-many relationship with TblProducts what happens here
since each product can have only one customer?

No, no, no! Relate Custoemrs with Products via TblCustomerProductLink.
Alternatively, add CustomerID FK to Products table. In this case, this
will probably be simpler than having TblCustomerProductLink.

Each service record can have one product, one customer and many parts(inventory)
TblServiceRecords many-to-many relationship to TblInventory via TblSRInventoryLink
Correct.

Since TblCustomer already has many-to-many relationship with TblServiceRecords what happens
here since each service record can have only one customer?

Nothing. It is the same relationship. This is a true one-to-many
relationship. You will use a form-subform system to filter
ServiceRecords by the Customer.

Since TblProducts already has many-to-many relationship with TblServiceRecords what happens
here since each service record can have only one product?

Same as above. Just imagine one-to-many as a big data set filtered by
the criteria chosen on the One side (Product, in this case). Say, you
would be able to see how many service recorsd exist for a given product,
and not sell that bad product anymore (or sell it more to service it
more

need to be able to query/lookup service records based on customer or serial number(not sure
which is best)
If by customer would have to select serial number then service date

If by serial number would have to select service date(seems like the better choice)

I would do both. It is not complex enough to bother choosing.

Does serial number belong in the products table or the service records table?
Same Make and Model may apply to many different Serial numbers

This depends a lot on whether you use the same models for many
customers. If yes, you will need to split up your Products side of the
database. Set up Installations table separately, Products table
separately, and then link them all to the rest of the DB.
If you do not reuse Products often, put SN in the Products table and
live with some redundancy in Make and Model.

As a matter of fact, I just helped someone else here in the NG set up
something very similar and still have the sample database I wrote for
them. I would not like to offend tem so if you'd like to see that
example, email me for the URL.
Good luck,
Pavel

 

[steveh]

Thanks much Pavel! Please forgive my ignorance here. I have a few more questions if you don't mind(see below

----- Pavel Romashkin wrote: ----

tblCustomer may benefit from adding contact information, like address
phone # etc. If you have complex contact information, this may need t
be further split in 2 other tables (unlikely)
I think it is best to use Autonumber PK for tblProducts, not the S
field. It is possible that two completely unrelated products might hav
the same SN
In tblServiceRecords, all is Ok except Parts Used field is not going t
work at al

TblSRInventoryLink needs to have a composite PK consisting of SRID an
InventoryID, and CustomerID, too

tblInventory is a little too simplified, but this may be what's yo
need. I think, inventory keeping is a bit more involved. Ideally, you'
want tblInventoryItems and tblInventoryTransactions, where you'd ente
inventory aquisition details. This may come in handy if you have to us
LIFO for your inventory accounting

Each customer can have many products and service records
TblCustomer Many-to-many relationship to TblProducts and TblServiceRecords vi
TblCustomerLin

No, no relationship with Products. It will relate to Products vi
TblCustomerProductLink. But there has to be CustomerID FK i
tblServiceRecords. However, you can add CustomerID FK to Products an
get rid of TblCustomerProductLink
and TblCustomer one-to-many relationship to TblProducts

Each product can have many service records but only one custome
TblProducts One-to-many relationship to TblServiceRecord
Right

Since TblCustomer already has many-to-many relationship with TblProducts what happens her
since each product can have only one customer

No, no, no! Relate Custoemrs with Products via TblCustomerProductLink
Alternatively, add CustomerID FK to Products table. In this case, thi
will probably be simpler than having TblCustomerProductLink

Each service record can have one product, one customer and many parts(inventory
TblServiceRecords many-to-many relationship to TblInventory via TblSRInventoryLin
Correct

Since TblCustomer already has many-to-many relationship with TblServiceRecords what happen
here since each service record can have only one customer

Nothing. It is the same relationship. This is a true one-to-man
relationship. You will use a form-subform system to filte
ServiceRecords by the Customer

Since TblProducts already has many-to-many relationship with TblServiceRecords what happen
here since each service record can have only one product

Same as above. Just imagine one-to-many as a big data set filtered b
the criteria chosen on the One side (Product, in this case). Say, yo
would be able to see how many service recorsd exist for a given product
and not sell that bad product anymore (or sell it more to service i
more :-

need to be able to query/lookup service records based on customer or serial number(not sur
which is best
If by customer would have to select serial number then service dat

I would do both. It is not complex enough to bother choosing

Does serial number belong in the products table or the service records table?
Same Make and Model may apply to many different Serial numbers

This depends a lot on whether you use the same models for many
customers. If yes, you will need to split up your Products side of the
database. Set up Installations table separately, Products table
separately, and then link them all to the rest of the DB.
If you do not reuse Products often, put SN in the Products table and
live with some redundancy in Make and Model.


As a matter of fact, I just helped someone else here in the NG set up
something very similar and still have the sample database I wrote for
them. I would not like to offend tem so if you'd like to see that
example, email me for the URL.
Good luck,
Pavel

 

[Pavel Romashkin]

See responses throughout the message.
the tblInventoryTransactions suggested below to records parts used as
opposed to parts acquired?

TblSRInventoryLink needs to have a composite PK consisting of SRID and
InventoryID, and CustomerID, too.

A composite PK is primary key that uses 2 or more fields together to
uniquely identify records. Each part of the compositekey can have
repetitive values, but their combinations must be unique. For example,
each CustomerID, SN and ServiceDate combination is unique in
ServiceRecords table. To make a composite PK, select the field in Design
view, go to Edit and select Primary Key.

In this case you can simplify itthe way you did, and simply update item
count in the same table where items are described.

No, no relationship with Products. It will relate to Products via
TblCustomerProductLink. But there has to be CustomerID FK in
tblServiceRecords. However, you can add CustomerID FK to Products and
get rid of TblCustomerProductLink.

and TblCustomer one-to-many relationship to TblProducts?

That's not what I had in mind, but it could work. If you do this, each
of your Products will be tied to only one Customer, and each record in
Products can then have SerialNumber. You will not be able to recycle
Products and may start accumulating redundant (and inevitably erroneous)
data if many customers have the same Product with different SN. This is
illustrated in the sample db, where Products are linked with Customers
via CustomerLocations junction table.

Definitely right! You need Products table (general description of
products you service) that is related to Customers via ServiceRecords
table. ServiceRecords will have SRID (autonumber), CustomerID,
ProductID, ServiceDate and SerialNumber. Now, you will use PartsUsed
junction table with fields SRID, PartID (from Inventory) to record parts
used for each service.

Hope this helps,
Pavel