0

A little background

We are adding a new feature to our software which allows users to enter text for a specific Customer or Location. The software will then read these from the database when a Customer or Location is chosen, and display the appropriate text in a pop-up window. Locations must have a Customer but a Customer can have no Locations. There are two scenarios that we would show these notifications:

  1. A Customer is selected from a drop down control and the software will look up any notifications for that Customer.
  2. A Location is selected from a drop down control and the software will automatically select the associated Customer and display any notifications for both.

I originally thought about creating two tables: CustomerNotifications and LocationNotifications, but when I added the fields for both, I realized the only difference is one table references a CustomerId while the other references a LocationId:

CustomerNotifications

Customer Notifications Table

LocationNotifications

Location Notification Table

I was thinking of combining the two to look like this:

Combined

enter image description here

Allowing NULL on both CustomerId and LocationId in case the notification is for one specifically. Is this the best approach or should I keep the table separate? Will I run into join issues if trying to retrieve both notifications but one is NULL?

My past tells me I have more flexibility with two tables but the "normalization" in me wants to try to condense them.

1

This is often done as a single Note table, and then seperate join tables to allow for multiple records.

So the tables you end up having are Customer, Location, CustomerNote (CustomerId, NoteId), LocationNote (LocationId, NoteId), Note.

I can't tell you the benefits or negatives of it but that's how it's done.

0

Keep it easy, use only one table and play with outer join on locationid columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.