Geek Answers Handbook

Best way to implement note table for many table objects in SQL

I have many tables: clients, prospects, friends ..
Everyone has notes.

Question 1: Should I have one annotation table shared by all parent tables.
OR
Should I have NotesCustomer, NotesProspects, NotesFriends Tables?

Question 2:
If the best solution is the first, then this general annotation table should have FK for the parent table, but ALSO the type of the parent table?

I am using EntityFramework, but this question is also general, I think ...

Thanks, John

+4
source share
5 answers

The first option, a separate note table for Vista, Clients and Friends, is probably a cleaner solution.

If you use a common annotation table, you need to have three FK columns with a null value to determine which related table FK belongs to, with only one of them being filled in each row.

Generally speaking, the goal of a good relational model is NOT (or not necessarily) to be stored as data together, but only to store certain data once. Therefore, a good question, ask yourself when developing this question, is "what will I get by keeping all the notes in one table?"

+6
source

The first option is the best one Notes table, shared by everyone else.

you can use the two notes_obj_id and notes_obj_type fields to implement the General Notes table

+1
source

I would prefer one approach to the table, but it also has its drawbacks. Pro one-table: Creating multiple tables with the same structure can be painful if you ever need to change their structure. In addition, you will have to enter a variable for the table name whenever you request notes instead of a logical "type" parameter. However, Phil's arguments are also interesting. You may also find that a layout from one table may end up in a database that cannot be queried more easily using only SQL. If you have a lot of data, having different tables will also give you a difference in speed.

Hm. A very clean, but also somewhat complicated solution is to create a NotableObject table. Then give each client a perspective, regardless of the "NotableObjectID" field, and attach notes to NotableObjects, and not to clients or potential clients. Of course, this complicates the situation if you want something like “give me all the notes about the customers”, because you explicitly save only the information from the client so that you can note and not reverse, but since most of the time you will have a situation, give me all the notes for THIS customer, "you may be fine.

0
source

You must have only one Notes table. The relationship comes from Notes to other objects (this is 0: M), so there is no need to have FK columns at the level of the Notes table. The FK columns in “Perspective”, “Client, friend” in the “Notes” table only lead to a design in which you will need to add FK columns to the “Notes” table every time a new object needs Notes (and this does not speed up the work actually),

For example, if you want to get a list of all Prospect notes, just query the Prospect table and use the join if you need to get the notes details:

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

A Notes table might look something like this:

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

In the remaining tables, all you need is the FK field associated with the NoteId in the Notes table.

0
source

I understand that this is old, but ..

This will make a few notes.

 select n.NoteId, n.NotesDetail from Prospect p inner join Notes n on n.EntityId = p.id AND n.EntityType ='prospect'
0
source

More articles:


All Articles