General database advice requested

[cheyanne]

I am designing a relational database for a Veterinary Surgeon friend of mine.



The practice has around 1500 clients and over 2,000 pets. A client can, of course, have more
than one pet.

I have designed several tables e.g. Client, Pets and Visits and am concerned that I am breaking
the rules on normalisation. For example in the Pets table I not only have the Client's reference number,
but also the Client's Surname and FirstName which of course also exist in the Client's table.

My concern is how do I best give the Veterinary Surgeon an efficient way to find which Client owns a specific
pet when viewing the Pet's records on the Pets form? Currently I use a combo box, which uses the table Client,
as its source, and relates to the two fields Surname and Firstname which are bound on the Pets form. I have
entered the two fields on the Pets table and this successfully shows, and retains, the Surname and FirstName
for each record.

Is this database construction correct? If not, what is the best way to show fields from other tables on, in this case,
the Pet's form? The data must be available as one views each record. I am unsure as to whether, if I made the
fields related to the combo box unbound, this would achieve what I need. Could a sub form be a possible solution?

Any advice would be much appreciated


Regards

Cheyanne

[R_Badger]

Ideally you shouldn't duplicate data between tables, if the first name and last name fields are located in another table you only need the client_id to relate to these and use a simple query to join the fields if that makes sense. you can then just use a control to display this, assuming you use the query as the record source for the form or report.

[recyan]

Apart from R_Badger's suggestion, was wondering, whether, One wo/man's pet Today could be another wo/man's pet Tomorrow.

Thanks

[orange]

There is a free data model that may be helpful to you. It may give you some ideas, at least.

http://www.databaseanswers.org/data_...tice/index.htm