A flexible foreign key

Question

I have a sql server database. I'm developing a vb.net application.

Now I'm thinking to have an "Event" table that should keep all the events related with my database. But one of the fields of this table should be a field ObjectID that is related with the record that this event has to do. But this record may be on different tables. For example :

Event 1 ---- Record 25 on table Clients
Event 2 ---- Record 30 Table Invoices
Event 3 ---- Record 40 Table Articles ...

The problem is that this field ObjectID should be a Foreign key in a flexible way , because may be related with different tables.

Is there any way I can resolve this case ?

Thank you !

can you give an example of a `Client Event` and a `Invoice Event`? You likely just need a qualifier column on `Event` to indicate what type of event each is. Hard to tell exactly what you are asking — Ňɏssa Pøngjǣrdenlarp, May 29 '15 at 21:14
You can index it to make joins faster. But you'll have to have some other column to define what other table the objectId apart of. Or you'll have to have a nullable column for each table you want to have events for and set the foreign key on each column. — jwatts1980, May 29 '15 at 21:16
Possible duplicate: [SQL Server foreign key to multiple tables](http://stackoverflow.com/questions/15935402/sql-server-foreign-key-to-multiple-tables) — Josh Part, May 29 '15 at 21:29
@Plitonix My problem is how can I read the specific record on specific table for which this event is created? I'm using Entity Framework.. — alex, May 30 '15 at 00:24
Since Entity Framework come out often, should it be added as a tag for this question? — AXMIM, Jun 01 '15 at 14:54
With entity framework, there is an inherited notions that may be what you are looking for. It is close to Zohar's solution. See those article here. However, I know know how much of this can be implemented without code first. http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph — AXMIM, Jun 01 '15 at 15:02
Possible duplicate of [SQL Server foreign key to multiple tables](https://stackoverflow.com/questions/15935402/sql-server-foreign-key-to-multiple-tables) — philipxy, Dec 16 '17 at 03:42

Zohar Peled · Answer 1 · 2015-05-30T12:52:36.153

One way to solve it would be to add a table to your database to act like a base for the other tables and connet it with a one to one relationship to the other tables, and then connect the events table to this base table.
This will allow you to keep data integrity for each of the tables.
The base table can be as simple as just one column, or can have columns thay all other tables have in common, thus implementing a sort of "inheritance" in your data structure.

Create the base table (assuming no common columns between other tables):

CREATE TABLE TblObjectBase 
(
    ObjectBase_Id int IDENTITY(1,1) PRIMARY KEY
)

Then, for any other table that needs to be referenced by the ObjectId in the Events table:

CREATE TABLE TblClients 
(
    Client_Id int PRIMARY KEY,
    Client_FirstName varchar(10),
    Client_LastName varchar(10),
    --  Other client related data
    CONSTRAINT FK_TblClients_TblObjectBase
               FOREIGN KEY(Client_Id) 
               REFERENCES TblObjectBase(ObjectBase_Id)
)

CREATE TABLE TblInvoices
(
    Invoice_Id int PRIMARY KEY,
    -- other incoice related data
     CONSTRAINT FK_TblInvoices_TblObjectBase
               FOREIGN KEY(Invoice_Id) 
               REFERENCES TblObjectBase(ObjectBase_Id)
)

The only thing remaining is to insert a new value to the TblObjectBase for any insert on your other tables. This can be be easily achived by either stored procedures or instead of insert triggers.
An insert procedure could look like this:

CREATE PROCEDURE Insert_TblClients
(
    @Client_FirstName varchar(10),
    @Client_LastName varchar(10),
    -- any other client related data you might have
)
AS
DECLARE @ClientId int

-- Insert a new record to the base table:
INSERT INTO TblObjectBase DEFAULT VALUES;

-- Get the id you've just inserted:
SELECT @ClientId = SCOPE_IDENTITY();

-- Insert the data to the clients table:
INSERT INTO TblClients 
(Client_Id, Client_FirstName, Client_LastName.....) VALUES
(@ClientId, @Client_FirstName, @Client_LastName...)

An instead of insert trigger would look like this:

CREATE TRIGGER TblClients_IO_Insert ON TblClients INSTEAD OF INSERT 
AS
BEGIN

DECLARE @ClientId int

-- Insert a new record to the base table:
INSERT INTO TblObjectBase DEFAULT VALUES;

-- Get the id you've just inserted:
SELECT @ClientId = SCOPE_IDENTITY();

INSERT INTO TblClients 
(Client_Id, Client_FirstName, Client_LastName.....) 
SELECT @ClientId, Client_FirstName, Client_LastName..... 
FROM inserted

END

If you choose to go with the instead of insert, the fact that the Identity value comes from another table should be transparent to the client (your vb.net program).

do you think this idea is suitable with my case , since I'm using Entity Framework on my application ? — alex, May 30 '15 at 00:30
I"ve never worked with entity framework so I can't say., I just suggested a way to keep the fk relationship. Why don't you try just a couple of tables and see if EF can handle it? — Zohar Peled, May 30 '15 at 05:42
ok , but could you explain to me , how this base table will connect to other tables , so I need one field as FK for each table or ??? — alex, May 30 '15 at 07:15
what I don't see in your model is how can I read an event. I mean if I want to get the specific record in the specific table that is related with an event ? — alex, May 30 '15 at 22:15
Well, in this model all the tables that are connected with a 1:1 relationship basically share a single identity column. This means that the client_id value can't be the same as an Invoices_id column. You can add a column to the TblObjectBase table that will state the name of the table the records refers to if it will make your code easier, or maybe a column to the events table that will state the object type that reaised the event. However, it's not needed for relational integrity, as the id columns for all the tables that can raise events are exclusive within all the tables that share the 1:1 — Zohar Peled, May 31 '15 at 08:10

AXMIM · Answer 2 · 2015-06-01T14:56:25.197

SQL Server doesn't support creating such constraint.

But you can simulate the link programmatically without much trouble.

CREATE TABLE tbl_Event
(
    [idEvent]       INT IDENTITY(1,1) NOT NULL,
    [TableSource]   INT NOT NULL,
    [SourceId]      INT NOT NULL
    --Events fields
    CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In the above exemple, SourceId is the foreign key
TableSource is used to know which table the foreign key is from.
In table source you could use sys.objects.object_id of the table.
However, since you don't have much control on those keys that are managed by SQL, I recommend using you own table with defined constant for each table instead of sys.objects.
This way you also have more control on which table can have foreign key in this table and it become really handy overtime.

CREATE TABLE tbl_tableSource(
    [idTableSource] INT IDENTITY(1,1) NOT NULL,
    [Constant]      INT NOT NULL,
    [Name]          NVARCHAR(255) NULL
 CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
(
    [idTableSource] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tbl_tableSource
VALUES(1000, 'tbl_SomeTable')

INSERT INTO tbl_tableSource
VALUES(2000, 'tbl_SomeOtherTable')

Also, this kind of relation, is less good for performance then standard one. So the field type of your keys and of the constant is really important. It should not be heavy. This because you will need to create an index on tbl_event.

To simulate cascade delete ON, you need to implement trigger on parent table like this :

CREATE TRIGGER OneParentTableOnDelete ON  tbl_SomeTable
FOR DELETE
AS 
BEGIN

    DELETE tbl_Event
    FROM DELETED
    INNER JOIN tbl_Event ON tbl_Event.TableSource = [Constant for tbl_SomeTable]
                         AND tbl_Event.idSource = DELETED.id --PrimaryKey


END

To retrieve data, you can then do like this

--For events about one foreign key
SELECT *
FROM tbl_event
WHERE tbl_Event.TableSource = [Constant for tbl_SomeTable]
AND tbl_Event.idSource = @idPrimareyKeyOfSomeTable

--Fore events about multiple foreign key
SELECT *
FROM [tbl_SomeTable]
INNER JOIN tbl_event ON tbl_Event.TableSource = [Constant for tbl_SomeTable]
                      AND tbl_Event.idSource = [tbl_SomeTable].id --PrimaryKey

on my VB.net program , I use entity Framework. Is it possible to construct an EF query to get data with your structure ? — alex, May 30 '15 at 00:22
I got really few experience with Entity Framework. The part that may not work with Entity Framework are triggers. http://stackoverflow.com/questions/21943314/entity-framework-6-code-first-trigger — AXMIM, Jun 01 '15 at 14:46

score 0 · Answer 3 · answered Jun 01 '15 at 15:07

0

You could create an 'INDEXED VIEW' -- define it however you like to pull the objectIDs + any other relevant fields, as you've described, from record 25 of the clients table, record record 30 of the invoices table, and so on.

You could do the same thing with a stored procedure.

Depending on how you intend to consume the data -- if you're just pulling it into a vb.net datatable, either of these options should work fine.

A benefit to an approach like this is that you don't have to create or maintain any new tables in your database.

answered Jun 01 '15 at 15:07

Chains

12,541
8
45
62

More here on stored procedures: https://msdn.microsoft.com/en-us/library/ms187926.aspx – Chains Jun 01 '15 at 15:08
Do you think that this solution , can work in my application that use Entity Framework 6 ? – alex Jun 01 '15 at 19:52
You can map entities to stored procedures (some examples here: https://msdn.microsoft.com/en-us/data/gg699321.aspx). You can map entities to views as well. You don't have to build your queries in the database -- usually I prefer to -- but you could just as well build your entities by querying directly in from application as well. Lots of options. – Chains Jun 03 '15 at 15:02

A flexible foreign key

3 Answers3

Linked

Related