2

I have following two DB tables. The purpose of "attributes" table is to provide user the ability to introduce more fields for existing tables at the runtime. So all user-defined attributes for all tables will be stored in one "attributes" table. There is no physical constraint on database. My question is how to make association between these two tables in Entity Framework 6?

enter image description here

enter image description here

Sadiq Khoja
  • 522
  • 1
  • 5
  • 23
  • This is called *polymorphic associations* and you'll be discouraged if you read this: http://stackoverflow.com/q/13953675/861716 – Gert Arnold Oct 09 '15 at 20:45
  • 1
    yes this looks almost like mapping TPH. If using database-first, it's almost straight-forward (the disciminator here is `tableName`). If using code-first, you need to map it correctly, see this https://msdn.microsoft.com/en-us/data/jj591617 – King King Oct 09 '15 at 23:26

1 Answers1

2

Re-design your database to have a table that links between user-defined attribute holders (e.g. schools) and the attributes themselves:

CREATE TABLE Schools (
    Id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name nvarchar(50) NOT NULL,
    AttributeOwnerId bigint -- This column should have both a FOREIGN KEY constraint on AttributeOwners.Id and a UNIQUE constraint (so no two schools can share the same user-defined attributes)
)

CREATE TABLE AttributeOwners (
    Id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL
)

CREATE TABLE Attributes (
    Id bigint IDENTITY(1,1) PRIMARY KEY NOT NULL
    AttributeOwnerId bigint -- FOREIGN KEY constraint on AttributeOwners.Id
    Name nvarchar(50),
    Value nvarchar(50)
)

By having this design you can now have database-level referential integrity for user-defined fields. Entity Framework will expose this as an Attributes collection on each School entity (or any other entity that has an FK relationship with Attributes via AttributeOwners).

There is a small design bug in that if you have two tables Schools and Colleges which both link to AttributeOwners then they could both point to the same AttributeOwners.Id value so they would share user-defiend attribute values. You can mitigate this by using a CHECK CONSTRAINT that checks other tables (by way of a UDF), however this will need to be updated whenever new tables are added to your design.

Dai
  • 141,631
  • 28
  • 261
  • 374
  • can't do that because AttributeHolder table will become huge. any way to do this by using tablename – Sadiq Khoja Oct 09 '15 at 18:45
  • @SadiqKhoja How huge are we talking? Remember that SQL Server can handle tables with *billions* of rows without any performance issues, especially considering the table only has 1 column. – Dai Oct 09 '15 at 18:50
  • @SadiqKhoja This design means you only need 8 or 16 extra bytes for each object (depending on the size of your primary keys (`int` or `bigint`) assuming that they all even need user-defined attributes in the first place. – Dai Oct 09 '15 at 18:52
  • @Dai yes more than billion records + number of insert and delete operations will increase as well – Sadiq Khoja Oct 09 '15 at 18:54
  • @SadiqKhoja I am very skeptical that your system actually has a billion rows though. Using your system where you store the table name (in `attributes.TableName`) for each attribute value will use **considerablly** more space than my solution. – Dai Oct 09 '15 at 18:55
  • lets see if somebody else have a better solution otherwise I may go with this one – Sadiq Khoja Oct 09 '15 at 18:57