Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: advice on planning database structure

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: advice on planning database structure

    Originally posted by leeolive
    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
    Most of the information that you have included is about the individual

    travaler. It's going to be one big table for the most part unless you want

    to separate certain sensative information into another table and link it in

    a one to one relationship.

    You could set up a postal code table and then a link table to include the

    cities that fall into that postal code. I'm sure you will see some

    repeating fields with the postal code, the city, state, country etc. but

    I'm not sure if the extra work is worth it. You'll have to be the judge.

    Sometimes normalizing a database structure is more work than it is

    worth. In my opinion at least.

    As far as validating the data and insuring that all fields that are required

    have been filled, you can use the properties in your table design view

    for each individual field. This will propagate through the whole database

    instead of just the individual form. Either way will work depending on

    what you need.

    Hope this helps. Good luck.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •