Database Design - please check

[lpy]

Hi, could someone please check that my design is ok?
Is there a better way to go through normalisation process?


I want to create a very simple yet effective database. No multiple users, just myself so no need to worry about validation data etc. The database is for Students and their work experience placemnets. However, there are cases where many students went to the same company, and some students did not complete placements at the HQ of the company but at different locations (offices).


I have tried to come up with a design and have also described the system I am trying to create a database for. Please ask, if you need more details and THANK YOU!!!:banghead:

Expected conditions:
One family – many students (siblings)
One student – many placements (some do 2 or three)
One company – many placements
One company – many locations (HQ, Various Offices)
One Placement – One visit by a member of staff

Tables:
Student
Family
Family Address
Placement
Company
Company Address
Placement Visit

Working examples:
Student A belongs to family A and has a placement with Company A at location A.
Student B belong to family B and has a placement with Company A at location B.
Student C belong to family A and has a placement with Company B at location C.
Student C belong to family A and has a second placement with Company A at location D.

Relationships:
1 Family – Many Students
1 Student – Many Placements
1 Company – Many Locations
1 Company – Many Placements

 

[GinaWhipp]

You're going to need a couple of Joiner tables or you will ne storing duplicate information.

Tables:
Student
Family
Family Address
tblStudentFamilies - NEW
Placement
tblStudentPlacements - NEW
Company
Company Address
Placement Visit

And let's remove all spaces, makes more work later on. Have a look at...

http://www.access-diva.com/d1.html

 

[lpy]

thank you. I have followed your advice and produced the following.

I am still not sure how to resolve the fact that a placement may take place at Company A, whose HQ is in London but the placement takes place in Birmingham for example. The complication arises when the following year another student is also placed with Company A but completes the placement in London. Recording the address in tlbPlacement would create duplicate data. Recording the placement address in tlbCompany does not allow for the first condition.

What am I missing?

thank you.

 

[GinaWhipp]

My day got away from but if no one gets to this by tomorrow morning... I will!

 

[lpy]

Hi guys,

I understand that you are all busy.
I have found some major mistakes in my design, already!

my suggestion for the missing link re: company-multipleaddress-multipleplacements

Tables (in addition to the rest)

Company
CompanyAddress
Placement - in this table I will have a field, CmpAddressID that look for 'A company name' and then provide a list of addresses that belong to that company.

for this to work I will have 1 Company 8 Many Company Address

1 Placement - 1 Address

is this right?

thanks

Lukas