SQL Theory Multiple References

Question

i am designing a database and have theory problem, about which solution works better to run queries, to be faster on microsoft sql server or simply more relational.

GIVEN

Lets say, we have the following Tables: Congress, Person, Session, Room, and much more. Don't mind about the given names. These are just some basic standalone entities.

-----------------------------------------------------------------
| Congress      | Person        | Session       | Room          |
-----------------------------------------------------------------
| CongressID    | PersonID      | SessionID     | RoomID        |
| Name          | Name          | Name          | Name          |
| ...           | ...           | ...           | ...           |
-----------------------------------------------------------------

Additionally we have a table called "Right". Rights have a name and can define access to something like one or many of the basic entities. Each person can have those rights assigned.

So there are 2 more tables: Right and PersonRight

---------------------------------
| Right         | PersonRight   |
---------------------------------
| RightID       | PersonRightID |
| Name          | PersonID      |
| ...           | RightID       |
| ...           | ...           |
---------------------------------

SOUGHT-AFTER

Now there is only one thing missing. The way or table that represents the relations to the other entities. I know three different ways that all will work, but i don't have the deep experience to decide which one will be the best.

1. The relational way?

Upgrade: For every new entity, add a new table

Relation: Right 1 : N Entities

Pros: Adding new entities doesn't affect the others in any way, foreign keys to entities

Cons: Many tables with maybe redundant columns like CreatedDate or rowguid.

SQL Example::

select *
from Right r
left join RightCongress rc on r.RightID     = rc.RightID
left join RightSession  rs on r.RightID     = rs.RightID
left join RightRoom     ro on r.RightID     = ro.RightID
left join Congress      ec on rc.CongressID = ec.CongressID
left join Session       es on rs.SessionID  = es.SessionID
left join Room          er on ro.RoomID     = er.RoomID 

-------------------------------------------------------
| RightCongress   | RightSession    | RightRoom       |
-------------------------------------------------------
| RightCongressID | RightSessionID  | RightRoomID     |
| RightID         | RightID         | RightID         |
| CongressID      | SessionID       | RoomID          |
| ...             | ...             | ...             |
-------------------------------------------------------

2. The column way?

2.1 The column way 1

Upgrade: For every new entity, add a new column to table "Right"

Relation: Right 1 : 1 Entities

Pros: No new table required, small statement, foreign keys to entities

Cons: Every new entity affect all other rows, only 1:1 relation possible, column count maybe confusing

SQL Example::

select *
from Right r
left join Congress ec on r.CongressID = ec.CongressID
left join Session  es on r.SessionID  = es.SessionID
left join Room     er on r.RoomID     = er.RoomID

-----------------
| Right         |
-----------------
| RightID       |
| Name          |
| CongressID    |
| SessionID     |
| RoomID        |
-----------------

2.2 The column way 2

Upgrade: For every new entity, add a new column to table "RightReference"

Relation: Right 1 : N Entities

Pros: 1:N relation, only one new table, small statement, foreign keys to entities

Cons: Every new entity affect all other rows, column count maybe confusing

SQL Example::

select *
from Right r
inner join RightReference rr on r.RightID on rr.RightID
left join Congress ec on rr.CongressID = ec.CongressID
left join Session  es on rr.SessionID  = es.SessionID
left join Room     er on rr.RoomID     = er.RoomID

---------------------------------------
| Right            | RightReference   |
---------------------------------------
| RightID          | RightReferenceID |
| Name             | RightID          |
| ...              | CongressID       |
| ...              | SessionID        |
| ...              | RoomID           |
| ...              | ...              |
---------------------------------------

3. The reference way

Upgrade: For every new entity, add a new row to RightReference with the new ReferenceTypeID

Relation: Right 1 : N Entities

Pros: Only one new table and dynamic references

Cons: Anonymous references and have always to remember the indexes to build queries, no foreign keys to entities

Explanation: ReferenceID is the primary ID of the referenced entity/row, like of table Congress, Session and so on. So you can't suggest to which table it references. For that reason there is ReferenceTypeID. It points to a translation table called ReferenceType, where every table is stored with an unique id. Maybe it is possible to use the system method OBJECT_ID instead.

SQL Example::

select *
from Right r
inner join RightReference rr on r.RightID = rr.RightID
left join Congress ec on rr.ReferenceID = CongressID and rr.ReferenceType = 1
left join Session  es on rr.ReferenceID = SessionID  and rr.ReferenceType = 2
left join Room     er on rr.ReferenceID = RoomID     and rr.ReferenceType = 3

----------------------------------------------------------
| Right            | RightReference   | ReferenceType    |
----------------------------------------------------------
| RightID          | RightReferenceID | ReferenceTypeID  |
| Name             | RightID          | Name             |
| ...              | ReferenceID      | ...              |
| ...              | ReferenceTypeID  | ...              |
| ...              | ...              | ...              |
----------------------------------------------------------

And now to all the sql experts.

What is the best or better lets say state of the art solution/way/approach to handle this task? If you have other ways, please let me know.

What i am Looking for is: General Advantages and Disadavantages, SQL-Performance, implementation difficulties with EntityFramework and everything else you know or think about it.

Thanks!

Answer 1

Usually when dealing with relational databases, anything that requires a schema change is a no-no because you have to do potentially dangerous operations on your SQL server, update EF as well as whatever models you may be using and probably redeploy whatever application serves as the frontend for your database.

The SQL Solution

If you're OK with committing a no-no every time a new entity is added or are for some other reason tied to an RDBMS, you have two options:

If you care about your entity(Congress, Session, Room) table schema

Column way #2 is probably the best idea because it separates relational data from actual table data. Make a separate table for the relationships between entities and rights and put an index on every possible entityId. In your example you'd need indices on CongressId, SessionId and RoomId columns.

If you don't care about your entity table schema

Combine all entity tables into one large Entities table with an Id column and an XML column that contains all your actual entity info such as the type. Single relationship between Entities and rights and you're good.

The NoSQL Solution

If you can go this route, it would probably suit the flexible structure you're looking for much better. You will still need to update the code that accesses the document store but judging from your proposal that seems unavoidable unless you have some extraordinarily-flexible-but-error-prone code in place.

You don't need to do schema/EF updates every time a new entity type is added, and you don't need to worry about relationships. Your Person objects will have all their rights nested right inside and will be stored in the document store exactly that way.