Design Question
Old forum URL: forums.lhotka.net/forums/t/8124.aspx
white911 posted on Thursday, December 10, 2009
I need to track
appointments. Each appointment has 2 people involved, who both need the
general person details as name, address, city, state, zip, phone, fax,
email etc. It's usually not repeatable, so I really don't need the
people in a separate table to reuse in another appointment. However, my
question is, for normalizing the database, I can create a people table,
and the main appointment table would have 2 FK id fields linking to the
people table, and it's always going to be a 1 to 1, since I will never
reuse the same person. By doing it this way, I have normalized the
database, since I did not repeat general fields, however, the CSLA
object to handle it, I have 2 ways
1) To have 1 class
'Appointment' and I will repeat all fields twice for each person, and
pass in all fields to a stored procedure which will save the data into
the correct tables and return the new PK id's for each table/record.
2)
Have a class for person and include it twice into the appointment class
as a single child class, once for each person, and then call it's own
save before saving the appointment. The problem is that I would need a
reference in the main table to all fields in the children person tables
in order to do form binding ???
Another approach to the whole
idea is to include all fields into the appointment table and repeat the
fields again for each person needed (we may need a third one too). The
database would not be normalized, however, the CSLA class would be like
any regular class since it only deals with one table.
vschaak replied on Friday, December 11, 2009
Hi,
without having a deep understanding of your problem domain it seems to me, that your table/Db isn't fully normalized.
Extracting all those name, adress etc. fields from the appointment is a good approach, but while reading "we may need a third one too" it seems that the process of normalizing the DB hasn't been finished!
Having fields like PersonID1, PersonID2 and PersonID3 in your table is a strong hint, that DB design may be flawed. Ask yourself whether there are ALWAYS (which definitly isn't MOSTLY) 2 Persons assigned to an appointment.
If issues occur, where you may have 1-3 (or even 0-n) persons, you should think of an appointment-table and a personToAppointment-table, the later one containing appointmentID and PersonID or all person-related fields, but only in case you definitly(again to be answered very strictly!) drop the concept of person-Identity.
By the way: You probably know radio jerewan? "In principle YES" does men NO to much of the above mentioned questions ;-)
Hope I expressed myself in an understandable manner, since english isn't my native language.
Kind regards
Volkerwhite911 replied on Wednesday, December 16, 2009
Thanks for responding,
In my case every appointment does always have 2 people, so it's not an issue with an appointment having less.
shawndewet replied on Wednesday, December 16, 2009
You certainly should have a separate Person class from an Appointment class. Similarly, the db should contain these separate tables. If there will always (and ONLY) be two persons for an appointment, it could make sense to have Person1ID and Person2ID as fields/properties on your Appointment table/class.
But the mention that there might need to be a 3rd person, leads to further ambiguity in that one could ask...well could there be a fourth person? and what about a 5th?
So to design for this, I would NOT have Person1ID and Person2ID defined on Appointment, but rather have as a 3rd table the PersonToAppointment table mentioned by Volker.
Then your Appointment class could have a MyPersons property that represents a collection of persons, retrieved via this table. Add business rules to Appointment to control the min and max records that can be in this collection as the business needs change.
Saving to the db would be done in a transaction, with Appointment as the txn root.Copyright (c) Marimer LLC