DB Design for a Service Company...

[Guest]

These customers are then allocated a 'service' code such as A.3460, M.3460,
S.3460. These codes relate to our three services:
A - Alarms, M - Mobiles, S - Static.

Some customers use just 1 service where others might use 2 or even all 3
services. Also, some customers have more than one site so they can have any
variation of services at any of their sites that we work at. This is where
our current database falls flat as the structure is all wrong!

What do you think of the following design and based on the limited
information above, can it be improved?

"Tables"
EMPLOYEES
Simple table detailing employees names, addresses, training courses, bank
details, licence numbers etc. 1 record per employee.

SUPPLIERS
Simple table detailing supplier names, addresses, product/service provided.
1 record per supplier.

CUSTOMERS
Simple table detailing customer names, addresses. 1 record per customer.

SITES
Some customers (not all) have several sites with different names, addresses.

SERVICES (not sure how to do this bit, as some customers/sites use 1, 2 or
all 3 services. The details/fields will be different depending on the actual
service)
serviceAlarm - details relating to alarm response
servicePatrol - details relating to mobile patrols
serviceStatic - details relating to static security

I realise that I'm only at stage 1 of building the database but I am keen to
get this bit right before moving on to stage 2. Any suggestions would be
greatly appreciated

Thanks.

 

[Guest]

Comments inline

EMPLOYEES
Simple table detailing employees names, addresses, training courses, bank
details, licence numbers etc. 1 record per employee.

Training courses should be in a separate table, with a linking table that
contains foreign keys to employees and training tables. This is a
many-to-many relationship.

SUPPLIERS
Simple table detailing supplier names, addresses, product/service provided.
1 record per supplier.

So a supplier only provides one product or service?

CUSTOMERS
Simple table detailing customer names, addresses. 1 record per customer.

SITES
Some customers (not all) have several sites with different names, addresses.

SERVICES (not sure how to do this bit, as some customers/sites use 1, 2 or
all 3 services. The details/fields will be different depending on the actual
service)

Put services in a separate table, with a linking table that contains foreign
keys to services and customers. This is a many-to-many relationship.

serviceAlarm - details relating to alarm response
servicePatrol - details relating to mobile patrols
serviceStatic - details relating to static security

Try looking here for tips on normalization:

http://support.microsoft.com/kb/283878/EN-US/

Basically . . .

1) Don't repeat groups in individual tables, such as using fields with the
names vendor1, vendor2, and vendor3.
2) Put all related data into their own tables. Customers place orders, but
customers and orders should each have separate tables.
3) Tables should only contain data that is related to each other in some way.
4) Use primary keys to identify the related data that is in its own table.
Each record in a table should contain specific data that is related through a
unique primary key.

Now, consider how each table relates to another. In a simple relationship

5) Use foreign keys to relate a record in one table with a record in
another table. If you have an orders table, and a customers table, you want
to show which customer has placed an order, so you include a foreign key in
the orders table, which will contain the same number as the primary key (from
the customers table) of the customer placing the order. Generally speaking,
you use the same name for the foreign key as you used for the primary key.

Once you have done those, then

6) Create separate tables for values that apply to multiple records. This
will be used in instances such as when you use a category to identify a
product. There can be many products that fall under the same category. You
can put all categories into a table, and then use a foreign key to signify
which category applies to a product.

In addition,

7) When there are values in two tables that relate many-to-many, you will
need to create a linking table between those two tables. This will be used
in instances such as when you have personnel who have training. The linking
table will have its own unique primary key, and will use a foreign key that
relates to a person, and a foreign key that relates to a training class.
This will enable you to have numerous classes for each person, or numerous
people for each class.

As a final task,

8) Open the relationship window, and create relationships between all of
the related primary and foreign keys. This will tell the database how
everything fits together.

 

[Guest]

It looks the first paragraph of my original post disappeared!

Yes, each supplier only supplies one service. It may sound a bit odd but our
suppliers are Alarm installation companies, Alarm monitoring stations,
Emergency Locksmith, Police, Fire etc.

I'm not sure I understand the part about 'Services'.
The Alarm Response service needs to record approx 20 fields including alarm
code, password, keyholders contact details etc.
The Mobile Patrol service needs to record approx 10 fields including number
of patrols per night, internal and/or external patrols, maintenance checks
etc.
The Static Guarding service needs to record approx 50 fields including site
based duties, shift duty patterns, number of hours per shift etc.

Should I create a detailed table for each of the specific services and then
create a linking table that simply shows each of the service names?

Thanks.

 

[Guest]

Hmm, I'm still struggling with the 'services' part of this design: -

Most of our customers have 1 site but some have 2 or more so I have set up a
'1 to Many' relationship between tblCustomers and tblSites.
The ContractID (pk) for each table is the same 6-digit field E.g. '000346'
Each new site is added to tblSites by entering the ContractID '000346' and
then adding a SiteID like 000346/01, 000346/02 etc. This seems to work fine.

I have created tblServices that currently contains 3 services
1 Alarm Response
2 Mobile Patrol
3 Static Guarding

Each site will be assigned 1, 2 or all 3 services and likewise, each service
will be assigned to several sites. A 'Many to Many' relationship between
tblSites and tblServices is obviously needed but what is a linking table and
how do I set this up?

Thanks.

 

[Guest]

Aha, I knew the 'Northwind' sample database would come in useful one day!

After a bit of research/head scratching, I created tblSitesServices which
contains SiteID and ServiceID fields. These are linked to the pk's in
tblSites and tblServices - it seems to work ok.

PS...
Am I allowed to answer/reply to my own questions?

 

[Guest]

Sorry to be a pain but I still can't get my head around this and it is
preventing me from getting any further forward.

tblCustomers "1:M" with tblSites
This appears to work fine as each customer can have any number of sites
associated to them. Clicking the + shows all sites for each customer.

tblSites "M:M" with tblsServices (using tbllinkSitesServices)
I 'think' this is ok, as each site can have 1, 2 or all 3 services
associated with them. However, clicking the + only shows the
tblServices.ServiceID number (E.g. "1", "2" or"3")

tblServices contains just two fields
ServiceID Service
1 Alarm Response
2 Mobile Patrol
3 Static Guarding
(autonumber)

The problem that I have is that each of the three services have a lot of
data associated with them so I think they should have their own individual
tables. How do I link these tables back to the tblSites?

SUMMARY
Customer = Bloggs Engineering Co (has 2 sites)
Site = Bloggs Gadgets (child of 1 customer)
Site = Bloggs Gizmos (child of 1 customer)

Bloggs Gadgets has been assigned all three services.
Bloggs Gizmos has only been assigned the third service.

ServiceID
1 = Alarm Response (20 fields)
2 = Mobile Patrols (10 fields)
3 = Static Guarding (50 fields)

Any suggestions? - please make it nice and simple

Thanks.