Table Structure - One to many?

indesisiv

Access - What's that?
Local time
Today, 17:42
Joined
Jun 13, 2002
Messages
265
Hi all,
I am currently trying to get a full list of GP's and practices in our local area together so that in a form you can use cascading combo boxes to select a GP name and from that select a surgery that the GP is at.

That bit is straight forward but after looking at it i am sure that there is a better way of doing this.

Currently i have a one to many table with GP's as the one and Practices as the many side. BUT after putting on surgery in there are five GP's in it so the surgery info is repeated 5 times in the Practices table.

I think that i will have to use some sort of many to many tables as each GP can be at more than one practice and each practice can have more than one GP.

How would i set up a relationship like this as i have never had to do it before and i am a little uncertain.

I have attached a picture of what my current relationship looks like.

Any help is greatly appreciated.
Steve
 

Attachments

  • CurrentRelationship.jpg
    CurrentRelationship.jpg
    8.1 KB · Views: 155
I use GPs in most of my dbs and the way I do it is have the GPs in a drop-down list, with the surgery already included. Because some GPs work in more than one surgery, I find that this is one of the easiest. You can begin to type in the name and then choose. Another way would be in your second combo box, is group the surgeries together - that way, only one would appear each time, but this could cause problems if the GP works at more than one surgery.

The relationships are just one-many - GP to Patient.

Not sure if this helps or not?!
 

Attachments

  • GP1.jpg
    GP1.jpg
    39 KB · Views: 163
  • GP.jpg
    GP.jpg
    8.2 KB · Views: 146
  • Rels.jpg
    Rels.jpg
    15.7 KB · Views: 139
Last edited:
Think that for full normalisation if the rule is GP (Many) to (Many) Practice you would decompose to

GP Table
GP PK

GP_Practice table
GP PK as PK and FK
Pract PK as PK and FK

Practice Table
Pract PK

One to many from PK's to FK's which also act as PK for the decomposed table.


Len B
 
Sorry mate i am having a nighmare ... i don't understand what you mean.
 
Thats okay Some of my explanations even I do not understand so I will try again

Okay so you have determioned that really there is a Many to Many relationship between GP's and Practices. As you know you cannot create this type of relationship so there is a need to do something about it.

So thinking about the GP table for the moment. It will/should have a primary key field. (PK)

Also the Practices table will have a primary key.

What you need is another table lets call it GP_Practice. This table will have at least two fields.

The first will be the same as the primary key in the GP table lets call it GP_1 and the second will be the same as the primary key in the Practices table, lets call it Practices_1. These two fields together will be the primary key of the GP_Practices table.

In order to make the relationship you join a Primary key to a linking field in the table you want to join to. This field is referred to as the Foreign Key.

So join the Primary Key of GP table to GP_1 (1 to many) and the primary key of Practices table to Practices_1 (1 to many).

Now a GP can be associated with Many Practices and a Practice can be associated with Many GP's and you cannot duplicate a GP and Practice because of Primary Key constraints.

Did I do better this time

Let me know if I did not explain it well enough

Len
So Join the Primary Key from GP table to the
 
OK i think that i have got it :D :D

Thanks mate
 

Users who are viewing this thread

Back
Top Bottom