Merge two tables or keep separate?

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

Merge two tables or keep separate?

LocationNotifications

Merge two tables or keep separate?

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

Combined

Merge two tables or keep separate?

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.

Replay

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.

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

Category: sql server Time: 2016-07-29 Views: 3

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.211 (s). 12 q(s)