Database design help.

  • Thread starter Thread starter kids23bball
  • Start date Start date
K

kids23bball

I am trying to make a db with client notes in it. My problem is I need to be
able to track the client name changes. For example: client comes in today
name is john doe, a note (form completed) is done and saved, now 3 months
later client comes in and name is john smith. I need the first note to still
be saved as john doe and not changeable and make sure john doe and john smith
are same person, which I have done by clientid. I want to be able to see all
notes done by john doe and john smith. Sorry if this makes no sense. I know
what I want just having trouble explaining. Thanks for any help.
 
as with any entity that requires historical data to be preserved, you need a
child table. your description is sketchy, so i'll show "sample" tables, and
let you apply the principles to your own design:

tblClients
ClientID (primary key)
<other fields that describe a client AND don't change OR don't need a
history of changes>

tblClientNames
NameID (primary key)
ClientID (foreign key from tblClients)
FirstName
LastName
DateEntered

tblClientNotes
NoteID (pk)
NameID (foreign key from tblClientNames)
Notes

relationships:
tblClients.ClientID 1:n tblClientNames.ClientID
tblClientNames.NameID 1:n tblClientNotes.NameID

you do *not* need to put the ClientID field in tblClientNotes, because each
note is directly linked to a specific clientname, which in turn is directly
linked to a specific client. for any given client, the newest (maximum) date
in the related ClientNames records will signify the "current" client name.

hth
 
I'm sorry, I'm not following you. Here's a better example of what I am asking.

11/8/08 Jane Doe, 101 main st, mytown, il comes in and a contact note is
typed on her and saved.
12/21/08 Jane Doe (now Jane Smith), 125 South St, yourtown, il comes back
and another note is typed and saved.

I need to make sure that when Jane's name & address changed the original
note stayed the same.

Thanks again for any help. Sorry I'm not following you.
 
Steve said:
TblClientDataNeverChanges
ClientDataNeverChangesID
<Client Data Fields That Never Change>

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve
Click to expand...

This is total nonsense. Do you actually read the requests before spewing
your nonsense?

John... Visio MVP
 
Tina,

Thank you very much for your response. It has helped tremendous. However,
I am still struggling. I know I should know this but I need an explanation
of the foreign keys.

"ClientID (foreign key from tblClients)" and also

the 1:n in this line

tblClients.ClientID 1:n tblClientNames.ClientID

Thanks again
 
you need to read up on the principles of relational design. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.
tblClients.ClientID 1:n tblClientNames.ClientID
Click to expand...

the above is a "shorthand" way of describing the table relationships:
ClientID is the primary key of tblClients, and it is the "one" side of a
one-to-many relationship with its' matching foreign key field ClientID in
tblClientNames.

hth
 
Back
Top