I would like to have a table pattern on my db. I.e. every column should have
The obvious approach is to add these columns on each tables, but is not so easy to mantain. I would like to have this informations shared across db in a single table. Schema of this table should be somthing like
The questions are
- how to join with other tables (two possible options are)
- 1) Use sys.tables table id as FOREIGN TABLE ID. May change? Should be dangerous in case of db refactory? Performance of query on sys.tables to retreive table name to join dynamically?
- 2) Add an extra field each table on db to have an integer code that identify it. I.e tableA.TABLE_ID=1, tableB.TABLE_ID=2, ..., tableXX.TABLE_ID=N. Using this approach a simple join is enough to match record. Difficult to use is a table have a multiple key.
- 3) REVERSE OF 2) Add an extra field each table on db to have an FK to shared table. In this case shared table have an IDENTITY() key instead of (FOREIGN_TABLE_ID, FOREIGN_ROW_ID)
- performance of each approach?