Data Schema 1

[oaklandar]

I am setting up a new Access 2000 database and want to make sure I am doing it correctly.

Here is how I am setting up my Schema(Data Model):

First Table called VisitorInformation:
Id
VisitorEmployer
RequestDate
PurposeOfVisit
DateOfVisit
POC //point of contact person you are visiting
POCphone
POCfax
Organization //organization you are visiting
VisitorOrgAddress //visitor's organization address
VisitorPhone
VisitorFax


Table two called VisitorPersonnel:
Id
PersonOneLastName
PersonOneFirstName
PersonOneMiddleInitial
PersonOneSSN
PersonOneBirthday
PersonOnePlaceOfBirth
PersonTwoLastName
PersonTwoFirstName
PersonTwoMiddleInitial
PersonTwoSSN
PersonTwoBirthday
PersonTwoPlaceOfBirth
PersonThreeLastName
PersonThreeFirstName
PersonThreeMiddleInitial
PersonThreeSSN
PersonThreeBirthday
PersonThreePlaceOfBirth
..and the fields continue with up to 10 Persons
PersonTenLastName
PersonTenFirstName
PersonTenMiddleInitial
PersonTenSSN
PersonTenBirthday
PersonTenPlaceOfBirth


Please advise If I am in the right direction or another better way of making this schema?

Is the first table going to have a one to many relationship with the second table using Id?

This database will be used with a Cold Fusion Web Application.

 

[LittleSmudge]

No, NO, NO, [red]Noooooo[/red]



Start simply

Start the name of the table with 'tbl'
The Id of the table should be the name of the table with the 'tbl' omitted and with 'Id' appended

tblVisitorInformation
VisitorInformationId
VisitorEmployer
RequestDate
PurposeOfVisit
DateOfVisit
POC //point of contact person you are visiting
POCphone
POCfax
Organization //organization you are visiting
VisitorOrgAddress //visitor's organization address
VisitorPhone
VisitorFax

This will make interpretation of code so much easier later on.


I'm uncomfortable about the Address not being Atomic
Consider :-
Address1
Address2
Address3
Town
County
PostCode

you can then search on Postcode or County or Town at a later stage for grouping and reporting purposes


now for more detailed issues:-

tblVisitorPersonnel
Can you HONESTLY say that for this application there will ALWAYS be TEN people's information to store ?

Can you ABSOLUTLY be sure that there will never, EVER, be more than TEN ?


No?
I didn't think so.

So lets have

tblVisitorPersonnel
VisitorPersonnelId
VisitorInformationRef // THIS is the Foreign Key to tblVisitorInformation
FirstName
MiddleInitial
LastName
SSN
Birthday
PlaceOfBirth

You can then have as many - or as few - Visitor Personel records as you need per VisitorInformation record.

( Note: Foreign Keys end in 'Ref' - not 'Id' so that, again, when you are looking at pages of VB code you can instantly tell if you are dealing with a PK or an FK and that table to which it refers )


( Note also that posting BLUE text on a blue background is NOT helpful. )




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

Read 'The Fundamentals of Relational Database Design'. It's a must read!

Leslie

 

[oaklandar]

Thanks!