Design and Relationship problem :(

[BetaMike]

Apologies in advance, as I realise that this is not the right newsgroup for
design questions but I'm getting a very limited response elsewhere. I have
worked with Access before but I have more experience with Excel so I need to
convert my thinking drastically. I am trying to design a database that will
manage our customers, our services, the customer sites, our service
instructions, site billing amounts etc.

We provide three very different services to our customers, which I think
will need to store the relevant info in three seperate tables (1 table per
service?)
Each customer (billing address) can have just one site or several sites
(delivery addresses).
Each site can use 1, 2 or all 3 of our services.

tblCustomers
ContractNo (PK)
CustomerInfo

tblSites
ContractNo
SiteNo (PK)
SiteInfo

tblServices
ServiceID (PK)
ServiceInfo

tbllinkSitesServices
SiteNo (FK)
ServiceID (FK)
SitesServicesInfo

tblService1
Acode (PK)

tblService2
Mcode (PK)

tblService3
Scode (PK)

tblCustomers currently has a '1 to Many' relationship with tblSites so that
1 customer can have several sites.
tblSites has a 'Many to Many' relationship with tblServices with the help of
tbllinkSitesServices so that 1 site can have several services and each
service can be assigned to several sites.

The above relationships allow me to successfully add a Customer and assign
numerous Sites to it. Each site can be successfully assigned any or all of
the Services. However, if I have our three service instructions in seperate
tables - how do I link them to the sites? I'm guessing here but should
tblSites have 3 columns for the Acode, Mcode and Scode?

Thanks.

 

[Michael Gramelspacher]

Apologies in advance, as I realise that this is not the right newsgroup for
design questions but I'm getting a very limited response elsewhere. I have
worked with Access before but I have more experience with Excel so I need to
convert my thinking drastically. I am trying to design a database that will
manage our customers, our services, the customer sites, our service
instructions, site billing amounts etc.

We provide three very different services to our customers, which I think
will need to store the relevant info in three seperate tables (1 table per
service?)
Each customer (billing address) can have just one site or several sites
(delivery addresses).
Each site can use 1, 2 or all 3 of our services.

tblCustomers
ContractNo (PK)
CustomerInfo

tblSites
ContractNo
SiteNo (PK)
SiteInfo

tblServices
ServiceID (PK)
ServiceInfo

tbllinkSitesServices
SiteNo (FK)
ServiceID (FK)
SitesServicesInfo

tblService1
Acode (PK)

tblService2
Mcode (PK)

tblService3
Scode (PK)

tblCustomers currently has a '1 to Many' relationship with tblSites so that
1 customer can have several sites.
tblSites has a 'Many to Many' relationship with tblServices with the help of
tbllinkSitesServices so that 1 site can have several services and each
service can be assigned to several sites.

The above relationships allow me to successfully add a Customer and assign
numerous Sites to it. Each site can be successfully assigned any or all of
the Services. However, if I have our three service instructions in seperate

Shouldn't there be a ServiceTypes table linked to Services instead of
splitting attributes into 3 separate tables?

Mike Gramelspacher

 

[Allen Browne]

There are several alternatives here.

One is, as you suggest, to have 3 foreign keys, even though only 1 of them
will normally be used at a time. This is simple to design, but can get messy
to query. Worse, it means a structural overhaul of the database the day they
decide to add a 4th service. This design is not really normalized, though we
probably do use it sometimes for things that are very simple and static.

It sounds like you have already considered the better design of having a
single tblService, with fields that cope with all 3 kinds of service, even
if many of those fields are not used most of the time. This is a more
normalized design, as it copes with future service types, and has none of
the query problems. Again, we probably do use this kind of thing for
simplicity, even though having all those empty fields in the table is not
strictly normalized.

The next level of design is to subclass your services. Your tblService
contains just the fields that are common to all 3 kinds of service, plus 3
related tables that contain the fields unique to each service type. Your
other table can have a single foreign key to tblService, and the related
tables are available so you can get those details when you need them. A
search on "subclass" should provide more info.

So, which one do you need? You need the *simplest* design that copes with
*every* likely scenario--as simple as possible, but it must handle
everything you need. We are not close enough to the design to know which one
you need.

 

[Sylvain Lafontaine]

If you *reall* need to separate your services in three separate tables, then
you need three different tables tbllinkSitesServices to maintains your three
N-N relationships between Sites and Services.

A second possibility would be to have a single Services tables but to split
the differences between these three services into three sub-tables. As I
suppose that each services will be billed, this is probably the simplest
design because all billing can be associate with a single table or a single
relationship.

 

[BetaMike]

Thanks for the suggestions gents

Allen,
I decided to go with your 2nd and 3rd paragraphs and I think I understood it
- here is what I did so please let me know if I got it wrong...

I added two identical fields called 'ServiceCode' to tblServices and
tblAlarmResponse
I then inserted a 'subdatasheet' in tblServices and pointed it to
tblAlarmResponse using the 'ServiceCode' field in the Child and Master boxes.

This has created a '1 to Many' relationship between the two tables. Is it
now simply a case of adding tblMobilePatrol and tblStaticGuarding tables and
repeating the above steps?

Thanks.

 

[Allen Browne]

That's the basic idea.

You end up with the 3 links out of tblService to the other tables, but the
main data table here (tbllinkSitesServices) has just one foreign key.

Normally, you would make ServiceCode the primary key of tblAlarmResponse as
well, so you end up with one-to-one relations between tblService and the
other 3.

To be honest, this 3rd option is not something I do very often.

 

[BetaMike]

Hi Allen,
Is there a way to post the 'relationships window' on here? I just want to
make sure it is setup correctly as I can't tell if a field is a PK or a FK.
The relationship line between the tables are blank but editing them shows
that they are '1 to Many' instead of '1 to 1'.

tblAlarmResponse has 75 records
tblMobilePatrol has 30 records
tblStaticGuarding has 20 records

When I open up tblServices and click the + to display the subdatasheets, it
only shows the 20 records in tblStaticGuarding. For some reason the
tblAlarmResponse and tblMobilePatrol have no records!

Thanks.

 

[Allen Browne]

In table design view, the primary key (PK) shows a key icon beside the
field.

The foreign key is not identified in table design view. In the relationships
window, it is marked with an infinity symbol (digit 8 turned sideways.)

 

[BetaMike]

Oh right, I thought that the infinity symbol meant that it was the 'Many'
side of a relationship!

I'll go through each table and check which fields are PK's and then check
the relationship window for the FK's.

Thanks

 

[Marshall Barton]

The infinity symbol does mean the "Many" side of the
relationship, which can only be to a foreign key.

Don't forget that a primary key must be unique. Since there
can only be one record with each value per table, a field
with a unique index is always on the "One" side of a
relationship. OTOH, a non-unique index must be on the
"Many" side.

A One-to-One relationship is where the linking fields in
both tables have a unique index. (Note that a One-to-One
relationship really means that there can be zero or one
records in the other table.)

 

[BetaMike]

Thanks everybody, it appears to work properly now
I'll create a few queries to see if the design stands up or falls over.

Thanks again to you all.