Design Question

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
Volker

white911 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