Best way to implement a Notes Table for many entities Tables in SQL

Question

i have many tables : customers, prospects, friends..
They all have some Notes.

Question 1: Should i have one Notes Table shared with all the parent Tables.
OR
Should i have a NotesCustomer, NotesProspects, NotesFriends Tables ?

Question 2:
If the best solution is the first one then this general Notes table should then have a FK to the parent table but ALSO the type of the parent table ?

I'm using EntityFramework but this question is also general i guess...

Thanks Jon

Answer 1

the first option is best one Notes Table shared with all the others.

you can use two fields notes_obj_id and notes_obj_type for implementing table general Notes

Answer 2

The first option, a distinct Notes table for Prospects, Customers, and Friends, is likely a cleaner solution.

If you use a general notes table, you will need to have three nullable FK columns to determine which related table the FK belongs to, with only one of them populated in each row.

Generally speaking, the goal of a good relational model is NOT (or not necessarily) to store like data together, but to only store specific data once. So a good question to ask yourself when designing this would be "what am I gaining by storing all notes in the same table?"

Answer 3

I'd intuitively prefer the one table-approach, but it has its drawbacks, too. Pro one-table: Creating several tables with the same structure can be a pain if you ever need to change their structure. Also, you'll have to put in a variable for the table name whenever you query notes instead of the logically cleaner "type" parameter. However, Phil's arguments is interesting as well. You may also find that a single-table layout may end up with a database that can't be queried anymore easily using just SQL. If you're going to have a lot of data, having different tables will also give you a speed difference.

Hm. A really clean but also somewhat complicated solution would be to create a table NotableObject. Then give each customer, prospect, whatever a field "NotableObjectID" and link the notes to NotableObjects, not to customers or prospect. Of course this complicates matters if you want something like "give me all notes on customers" because you explicitly store only the information from customer to note and not reverse, but since, most of the time, you'll have a situation more like "give me all notes for THIS customer", you might be fine.

Answer 4

You should have just one Notes table. The relationship flows from Notes to the other entities (it's a 0:M), so no need to have FK columns at the Notes table level. FK columns to the Prospect, Customer, Friend, on the Notes table only leads to a design where you will need to keep adding FK columns to the Notes table each time a new entity needs Notes (and this doesn't speed things up really).

E.g., if you want to get a list of all Prospect notes, simply query the Prospect table and use a join if you must get the notes detail:

select n.NoteId, n.NotesDetail from Prospect p inner join Notes n 
    on p.NoteId = n.NoteId

Your Notes table might look similar to this:

create table Notes
(
    NoteId int identity(1,1)
    ,NotesDetail varchar(max)
    // ... any other fields related to the Notes entity....
)

On the other tables, all you need is a field FK linking to NoteId on the Notes table.