advice on how to plan my tables

Hi!

I haven't been near Access or planned a database structure for about 2 years now and am battling to figure out the database design. The website will collect information on travellers visiting the UK and is to do with their pensions. The travellers will obviously have a birth country, permanent address and an address here in the UK.

Can someone help?

I have a total of 41 fields, but am not sure what to put into what table etc etc and which fields should link to each other. I would ideally like to keep things as simple as possible.

ApplicantID (auto generated)
Title
FirstName
Surname
MaidenName
MaritalStatus
Gender
DateofBirth
NINumber (National Insurance number)
Street
Town
City
County
PostalCode
Country
Email
HomePhoneContact
MobilePhoneContact
PermanentStreet
PermanentTown
PermanentCity
PermanentCounty
PermanentPostalCode
PermanentCountry
Country2 (country of origin/birth)
NOKName (next of kin name)
NOKDateofBirth (next of kin - date of birth)
Profession
EmploymentType
Employer
EmployersStreet
EmployersTown
EmployersCity
EmployersPostCode
WorkPhoneContact
DateJoinedCompany
DateJoinedScheme
AnnualSalary
RetirementAge
PolicyCommencementDate
PaymentArrangement

One more question: If a field is mandatory, should I set this to mandatory within the database or can I just ensure that it is completed within the form validation on the website? Ie. Is it 'risky' to set it to mandatory, will an error occur (that I would have to handle) on the website if for some reason that field is not completed?

Thanks, I appreciate this!
Lee

I'll answer your closing question first. With Access I've always found the setting of things in the database itself a bit risky. Usually with other parts of Access (forms, reports, queries).

My usual way of dealing with database/web systems is to leave the database without rules except for field types, so null values can be entered. Then I create the web site, which has to provide form validation as integral part of any web application.

During testing it becomes apparent where you need to set rules such as default values, usually where a record is inserted and you don't want to write a value for every field in the SQL statement.

As for your fields:

USER_TABLE
ApplicantID (auto generated)
Title
FirstName
Surname
MaidenName
MaritalStatus
Gender
DateofBirth
NINumber (National Insurance number)
Street
Town
City
County
PostalCode
Country
Email
HomePhoneContact
MobilePhoneContact
PermanentStreet
PermanentTown
PermanentCity
PermanentCounty
PermanentPostalCode
PermanentCountry
Country2 (country of origin/birth)
NOKName (next of kin name)
NOKDateofBirth (next of kin - date of birth)
----
Profession
EmploerID
EmploymentType
WorkPhoneContact
DateJoinedCompany
DateJoinedScheme
AnnualSalary
RetirementAge
PolicyCommencementDate
PaymentArrangement

EMPLOYER_TABLE
EmployerID
Employer
EmployersStreet
EmployersTown
EmployersCity
EmployersPostCode

As the bit in the users_table that I marked under --- is unique per user, this should be in the user table though it looks like it should be in the employer table.

Take a look here

http://www.devshed.com/Server_Side/...mal1/page1.html

Thanks Binky and Doug for taking the time to reply! I thought I would need to use 2 + tables(damn!). Very helpful link, Doug.
I like the way the guy says he can still remember how it felt to actually submit to a database from a website - I have to agree thatb it's such a *great* feeling to see the data appear within the table! (cause for many a sing and dance

Cheers
Lee