Re: Same table or new table
mpaulbattle, let me try to explain the type of decisions you have to make when you are going to normalize tables.
The first thing you need to do is to analyze what kind of entities you deal with. I'm going to take the issue of doctors and patients in a medical office as an example of the "right" line of questioning.
If you deal with people (doctors, patients, nurses, etc) that can be one type of entity or two (either all people, some of whom are doctors and some of whom are paients, or two entities to keep data separate). Deciding on one or two types of entity will depend on the business rules regarding the way the application must treat the two.
If patients have visits with doctors, the record of the individual visit is another type of entity. The idea that a doctor has a "practice" means that you have a third type of entity. Some of the actions a doctor could perform are yet another entity, perhaps a "treatment." In brief, different entities each get their own table based on commonality of business model rules.
It is possible for these entities to be directly and indirectly related to each other. It is possible for multiple relationships between entities. When doing the data analysis, what you look for is the nature of the relationship.
For instance, if a patient visits a doctor, you have the "visit" which requires both a doctor and a patient. So that is a double-barreled dependency. You can have a patient and you can have a doctor whether or not they see each other, so the patient and doctor are independent entities. But the visit requires BOTH patient and doctor, so it is a dependent entity. The treatment occurs in the context of a visit, so treatments are dependent on visits. The doctor and patient can see each other many times (sort of the definition of a practice, I believe), so you can have a many-visits-per-patient and ditto for the doctor. Thus, you have a pair of many-to-one relationships. Which also means that each visit is a separate entity from other visits. So you need something about a visit to tell two visits (same doctor, same patient) apart, perhaps such as a date or a sequence number.
What good is this kind of discussion? Well, the entities that are dependent on something else higher in the hierarchy of your structure are the ones that must have foreign keys (FK). The things on which they depend are the ones that must have prime keys (PK). So this kind of question helps you decide where the keys will go and how they will be used. It IS possible to have both a PK and some FKs associated with the same entity.
So the next kind of question that you must decide is, what data goes where? Sometimes it is easy; sometimes, not so much. For the doctor-patient-visit-treatment structure, the doctor entity is described by a doctor table. The information about the doctor might include phones, addresses, certifications, etc. Things where the doctor only has one (such as "home address") might be in a Doctors table. Specialties or certificates, of which the doctor might have many, would go in a child table with a one-to-many relationship with the doctor.
The idea of a Practice depends on how you define it. If you have multiple doctors sharing a practice, a question would be whether they share patients as well, or whether the doctors only share offices and business staff but are exclusive to a given patient.
These are the questions you would ask when normalizing so that you can find the right number and content of tables. I know it can be completely confusing and frustrating - but if you have a good understanding of your business model, you should be able to get a good data layout quickly.
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.