Help getting design right first time (relationships)

[Creakyknees]

Hi everybody, I am looking for someone who has a little time and the patience to help me with the design of MS Access database. I am reasonably familiar with creating tables and queries, but it is the design of the structure of the database and the relationships I would appreciate help with and I want to get it right from the start.

So here is my scenario:

I want to write a database to store compulsory records for Landlords. Each landlord may manage several properties. For each property the records must be kept for a minimum of 5 years. Each record (such as fire safety checks, boiler inspections, servicability check) is currently a form that must be completed with many questions to each form. I envisage one table per form type. As far as possible I want to normalise the database with reusable tables storing addresses, etc. Any guidance, help and pointers would be greatly appreciated. Creakyknees.

[Creakyknees]

Ok, I will try and be a little more specific:

I have the following tables:

tblLandlord
PK IDLandlord
Forename
Surname
CompanyName

tblAddresses
PK IDLandlord
FK IDAddress
AddressLine1
AddressLine2
AddressTown
AddressPostCode
AddressCounty

tblContact
PK IDLandlord
CompanyAddressLine1
CompanyAddressLine2
CompanyTown
CompanyPostCode
CompanyCounty
CompanyTelephone
CompanyMobile
CompanyFax

Then a form for each Paper From that has to be completed like Boiler Checks:

tblBoiler
FK IDAddress
Date
Pass/Fail
Conducted by
Action taken

I have tried to keep all data normalised and the tables reusable. The main table is tblLandlord which is currently linked to tblAddress (1 to many) and tblContact (1 to many). Are the relationships correct?

Secondly how do I link each table such as tblBoiler to tblAddress for each location and what is the relationship I should use?

As the paper forms may change over time, do I need another table in the DB to index the forms and their details?

All the best, Creakyknees.

[June7]

A landlord can have many contacts? IDLandlord should be FK in tblContacts.

tblAddresses is the properties? Why would IDLandlord be a PK and IDAddress a FK? Isn't that backwards?

What do you mean by 'forms may change' - just adding fields?

[Creakyknees]

June7, thanks for your help so far. I have very limited experience in designing databases, which is why I may have got the keys back to front and hence why I asking for assistance here. You are correct, the tblAddresses is to store the details of the properties (probably better labelled properties!). I have searched the web high and low for a diagram of a schema which does something close, but have not managed to find one. I think a diagram would assist me best.

Regarding the forms changing I have a feeling that sometime in the future the details requested on the paper forms will change, so I am trying to futureproof the design of the database so that only minimal changes to tables occur and not changes to the structure of the database.

If I could just get the backbone of these tables and a couple of the tables to store what is currently on the paper forms, I will probably be able to complete the rest myself.

Creakyknees.

[June7]

If the 'details' are the content of fields and not adding or removing fields, then would need a record for each variation. I guess any report can be viewed as a 'boilerplate' document with variable data. Get the data structure correct then output to report should be fairly straightforward.

Maybe this site will help you get there. http://www.rogersaccesslibrary.com/