Merge two tables or keep separate?

Question

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

LocationNotifications

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

Combined

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.

Answer 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.

Answer 2

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