I must produce a table known as "NOTES". I believed this table would have a "table_title" VARCHAR(100) which signifies what table make the note, a "key" or multiple "key" posts representing the main key values on the table this note is applicable to along with a "note" area VARCHAR(MAX). When other tables make use of this table they'd supply THEIR primary key(s) as well as their "table_title" and obtain all of the notes connected using the primary key(s) they provided. However , other tables may have 1, two or more PKs and so i am searching for applying for grants the way i can design this...

What you are recommending sounds just a little convoluted in my experience. I recommend something similar to this.



Id - PK

NoteTypeId - FK to NoteTypes.Id




Id - PK

Description - This might replace the "table_title" column you recommended



Id - PK


Other Posts


NoteId - FK to Notes.Id

This could permit you to keep the data better stabilized, but nonetheless obtain the associations between data that you would like. Observe that this assumes single:1 relationship between rows inside your other tables and Notes. In the event that relationship will be numerous to 1, you will need a mix table.

Take a look only at that thread about database normalization

Furthermore, you should check this resource to explore foreign secrets

Rather than putting another table name's and primary key's within this table, possess the primary key from the NOTES table be NoteId. Create an FK in one another table that can make an email, and keep corresponding NoteId's within the other tables. You'll be able to simply join on NoteId readily available other tables towards the NOTES table.

When I understand your condition, you are trying to "abstract" the auditing of multiple tables in ways which you may abstract a category in OOP.

While it is a great OOP design principle, it falls flat in databases for multiple reasons. Possibly the biggest single reason is when you can't picture it, neither will someone (even you) searching in internet marketing later come with an easy time reassembling the information. More compact that that though, is the fact that when you often think about a table like a container and therefore much like an item, the truth is they're implemented cases of this hypothetical container you are attempting to construct and operate better should you treat them as a result. By creating an audit table specific to some table or perhaps a subset of tables that share structural similarity and data similarity, you boost the performance of the database and also you will not run directly into strange trigger or choose related issues later.

And also you can't picture it not because you are bad at what you are doing, but instead, the dwelling isn't favorable to database logging.

Rather, I would suggest that you simply create separate logging tables that manage the auditing of every table you need to audit or log. Actually, some fast google searches show many scripts already written to complete point about this tasking for you personally: Example of one such search

You need to create these individual tables after which if you wish to have the ability to set of multiple table as well as all tables at the same time, you may create a saved procedure (if you wish to make queries according to qualifying criterion) or perhaps a view by having an incorporated Choose statement that JOINs and/or UNIONs the tables you are looking at - in a way that is sensible towards the report type. You'll still need to write new objects to the view, but despite your original table design, you'd need to account for your.