Database design help.

[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.

 

[tina]

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

 

[sue gray]

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.

 

[John... Visio MVP]

TblClientDataNeverChanges
ClientDataNeverChangesID
<Client Data Fields That Never Change>

TblClientAlias
ClientAliasID
ClientDataNeverChangesID
VisitDate
Note

Steve

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

John... Visio MVP

 

[Steve]

You are also calling Tina a jerk; she gave the same answer.

 

[Keith Wilby]

You are also calling Tina a jerk; she gave the same answer.

Ummm ... no, no she did not.

 

[sue gray]

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

 

[tina]

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

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

 

[sue gray]

Thanks for the reply. I will do some more reading. Thanks again.

 

[Keith Wilby]

Wow, Dr Frankenstien meets MS Access.

 

[tina]

you're very welcome

Thanks for the reply. I will do some more reading. Thanks again.