Geek Answers Handbook

SQL table normalization and denormalization

I am using MS SQL Server 2012 . I have two tables, one for doctors and the other for surgical centers. Each table currently contains columns that describe how they (doctors and surgical centers) should receive reports (e-mail, fax ...). The columns that store this data in both tables are identical. I am going to pull out the delivery method columns from each table and compile a general table of delivery methods, but I will need to have foreign keys for both the doctor and the surgical center in each row. This is troublesome since there is one to one between the delivery method and the doctors. The same connection goes beyond the table of the surgical center.

Question:

  • Is it good to have a table with two foreign keys, and one of them will always be zero?
  • What is the best strategy to handle such a case.

The reason I would like to get information about the delivery method from the tables of doctors and surgical centers is to reduce the overall size of the tables. It will also improve the normalization of my data. I appreciate any help and guidance.

+4
source share
4 answers

You have a relationship going from the delivery method to dr / centers, and not from dr / center to the delivery method.

If you cancel the connection, you not only get rid of the need to have a table with a potentially inconsistent column, you can share delivery methods with several numerous drs and centers. You say that there is a 1 to 1 relationship, but if there is no business rule PREVENTING dr or center from the same delivery method as any other dr or cent, then what you have is actually a relationship " many to one "(many dr / centers can have the same delivery method), even if in practice this is unique.

0
source

Thought should have one DeliveryMethod table. Each entry in this table will have an identifier (probably a surrogate - for example, Identity or Sequence). Then the Doctor table will have a foreign key DeliveryMethodID, and also the table SurgeryCenter will have a foreign key DeliveryMethodID.

In a typical database design, you do not need two identical tables. If the size of the table becomes a problem, there are various approaches to solving this issue, including making it partitioned table .

+1
source

One option is to have two staging tables that associate PK tables of DeliveryMethod with Doctors and SurgeryCenter , so, for example, DoctorDeliveryMethod has two columns: Doctor.id and DeliveryMethod.id .

This requires you to join the three tables to get methods for each. Another is to associate DeliveryMethod.id with a column in the Doctor table, call it DeliveryMethodID . You will have to keep accounting anyway

0
source

Possible alternative design:

  • 'DeliveryMethods' table: column identifiers (PK), fax number, email address, preferred, ...
  • Table "SurgeryCentres": columns SC_ID (PK), name, ..., DeliveryMethodID (FK, referring to delivery methods), ...
  • Doctors table: columns MD_ID (PK), Name, ..., DeliveryMethodID (FK, referencing DeliveryMethods methods), ...

This avoids null values, but gives normalized data.

0
source

More articles:


All Articles