I am designing a database and I was curious about the best approach for a scenario

ex:
i have a client who has a client file and a medical profile and a financial profile would it be better to set up the client fiel, medical profile, and financial profile to all have foreign keys to the client or would it be better to have the client file carry foreign keys to the medical and financial profiles?


I also have something else. I want to gather data about the spouse of the client as well. I will be capturing the exact same data about both. Would it be better to split the tables up or just make one Person table that has a PersonTypeId field that is a foreign key to a PersonType table?

Any help would be appreciated. I have changed the Database already but I want to know your thoughts.

Thanks

Call me a database wussie, but I find that requirements change more often than underware. Unless the fundamental design of some REAL WORLD object determines how many of what can occur, I assume that the cardinality (count) is going to be zero to infinity. I have been proven right thousands of times for every once I was wrong on this topic!

I would create objects for your person, client file, medical profile, and financial profile. I would also create a new object for groups (of persons, presumably families). These tables would only have PKs, they would not include FKs to any of these other tables.

I would create linking tables for person to group, to client file, to medical profile, to financial profile. These linking tables would include the two related FKs (like person and client file), a BeginDate, an EndDate, and a Description. This linking process allows a person to not have a Financial or Medical profile when that data isn't available. It allows people to be related via groups (create a group with the persons name if they aren't related to anyone else). It allows people to have a relationship (such as a parent and their child) for a given period of time (defined by the begin and end dates), then to have the child become a client in their own right when that becomes appropriate.

This does add a smidgeon of complexity, but it saves you so much time and pain in the long run that I heartily recommend it!

-PatP

You're a big database wussie.