Table Structure

[HockeyNut]

Hi,
I was hoping for some advice as to if there were any glaring errors in my normalisation.

NOTE: Can I just say (as I've seen this happen)... I hope nobody gets on their high horse about me asking this. I'm not taking the pi$$ and getting you guys to design it, I've come this far, and just wanted opinions/guidance (I'm sure you were all there once).

---All Keys are in red---

These are my tables:

TblClient
ContactID
KPRNumber
Title
FirstName (Was Initial)
MiddleName
LastName (Was Surname)
Address1
Address2
City (Was Address3)
PostalCode (Was Post Code)
Region (Was District)
HomePhone
MobilePhone
Birthdate (Was DoB)
Deceased
DateEntered
ClientType (Was Establishment Y/N)
Establisment Name
PreviousAddress
Notes
AutoOrDiscret
Blind
ULD

TblPayment
PaymentID
ApplicationID
BadgeID
DatePaymentReceived
NameOnCheque
AmountPaid
Location
PamentMethod
PaymentReference
PayingInSlipID
StaffMemberChecking
StaffInputtingID

TblApplication
ApplicationID
KPRNumber
DateApplied (EnteredOnSystem)
RenewalorNew
RefusalReason
AplicationsNOTRenewedReason:
GPLetterSent
GPLetterDate
GPPaid

TblBadge
BadgeID
Date/issue
Date/exp

TblSecurity
StaffMemberID
StaffFirstName
StaffLastName
Password

TblPayingInSlip
PayingInSlipID
DatePaidAtHQ

MS Word Templates


Thank you,
kindest regards,

 

[HockeyNut]

Thanks a lot for your time and your reply Pat.

1. Special Charachters - Oops, if you mean the few where there are brackets, they were comments/remarks for my reference and I didn't mean to include them as field names. And they won't be included. However, the "/" I will remove, I didn't realise they were bad practice. Every day's a school day.

2. TblPayingInSlip - The TblPayingInSlip is because the cheques/PO's which are received are held in the safe and then paid in a few cheques at a time, which are summarised on a paying in slip. A PayingInSlip can have many Payments (cheques/PO's).

3. Foreign keys - I didn't realise I had done this with different names.
The StaffMember is a very good point, you were right with your latter comment on this. The StaffMemberChecking is also a foreign key, I will take your advise on that one with the new naming.

4. ContactId - I kind of put this in a secondary backup PK. Reason being, the new PK which I want to use is the KPRNumber, as this is unique to everyone. However, KPRNumber is not used in the existing database, but can be pulled in from a different larger client database. I thought ContactID (perhaps ClientID is a more appropriate description) would be usefull as a fallback incase there were problems extracting the KPRNumber from the other database. I would appreciate any thoughts you have on this area in particular?

5. PreviousAddress - Yes this lumped together several fields, I see your point, I will rectify that.

Thanks again Pat

Kindest regards,

 

[HockeyNut]

I've been working on this today. How's this? (See Attachment)

 

[HockeyNut]

Just anothr quick question.
On the elationship diagram it created a TblStaff_1 (as well as the original TblStaff). I assume this would be just for the purpose of the diagram, and it wouldn't have to actually work like this?
I assume it is because I've used the same field as a foriegn key twice, but for different purposes?

And thanks again for your great advice Pat.