Enforcing existence of records while avoiding circular references

database-designerdforeign keyreferential-integrity

I'm having issues designing a satisfactory solution for the following problem.

There are two main entities:

Customer
User

A customer can have many users associated to it. A user can be associated with many customers. Definitely a many-to-many relation for which a junction table should exist.

A customer must have at least one admin user. This is absolutely critical.

For several reasons it would be nice to enforce that the combination of customer & corresponding admin account exists in the junction table. For example: when listing the customer's users, it would be nice to not have to manually include the admin user.

In the following design it would be possible for the combination of customer & corresponding admin to not exist in the junction table:

(ignore the erroneously placed crow's foot at 'User')

Is there a good way to solve this problem?

The solutions I came up with so far involved introducing circular references, which I think are undesirable. Another option might be to go with this design and use a VIEW for the user listing. Feels like a workaround.

Is there a flaw in my design or is this a limitation of relational databases?

Best Answer

I think that your problem could be solved by imposing a new Foreign Key constraints in your tables:

User (UserId, UserOther) PK(UserId)
Customer (CustomerId, CustomerOther, UserAdminId) PK(CustomerId)
Customer_User (CustomerId, UserId), PK(CustomerId, UserId)

In Customer_User:
  FK(CustomerId) REFERENCES Customer
  FK(UserID) REFERENCES User

In Customer:
  FK(UserAdminId) REFERENCES User
  FK(CustomerId, UseAdminId) REFERENCES PK(Customer_User)

In this way you are guaranteed that a Customer has a valid User as admin, and that there is a tuple in Customer_User that corresponds to a the admin of a certain Customer.

Related Solutions

Sql-server – Representation of circular references constraint

All you need is to propagate TypeId down to the link table:

CREATE TABLE Commands(
    CommandId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniqueCommandNameInSpecificType UNIQUE(Name, DeviceTypeId),
-- added this constraint
    CONSTRAINT Commands_FkTarget UNIQUE(CommandId , DeviceTypeId)
);

CREATE TABLE DeviceProperties(
    PropertyId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniquePropertyInSpecificType UNIQUE(TypeId,Name),
-- added this constraint
    CONSTRAINT DeviceProperties_FkTarget UNIQUE(PropertyId , DeviceTypeId)
);

CREATE TABLE DevicePropertiesInCommands(
    CommandId int NOT NULL,
    PropertyId int NOT NULL,
        DeviceTypeId INT NOT NULL,
    OrderNoOfProperty int NOT NULL,
    PRIMARY KEY(CommandId, PropertyId),
    CONSTRAINT UniqueOrderNoInSpecificCommand UNIQUE(CommandId,OrderNoOfProperty),
        CONSTRAINT FK_DevicePropertiesInCommands_Commands 
            FOREIGN KEY(CommandId , DeviceTypeId ) REFERENCES Commands(CommandId , DeviceTypeId ),
        CONSTRAINT FK_DevicePropertiesInCommands_DeviceProperties 
            FOREIGN KEY(PropertyId , DeviceTypeId ) REFERENCES DeviceProperties(PropertyId , DeviceTypeId )
);

ERD Review Please

Usually, events are not infinite. There's more than one way to represent this in your database: either store an endTime, or a duration. You could make it optional if you don't want to force it on people, but if the event starts at 4 pm and someone shows up at 11 pm they will be disappointed that they missed the event, and your app was not obviously clear enough that the even ended at 9 pm. You might become the subject of their criticism (whether or not it's rational is a different matter).

You might also want to add a notes field that's just some text that anyone can add for special info for the event.

Also, this design does not allow for One Event Can Host Many Book Authors. From your diagram, it looks like one event can have exactly one author. You might need a new table such as this:

event_authors
-------------
   event_id
   author_id

If you make this table have a composite key of event_id and author_id, it will allow you to have multiple records with the same event and different authors. Of course, you'll have to remove the author_id reference from events.

As for One Author Can Only attend One Event at a time., this one is a little trickier. Your current schema does not enforce this, but I suppose you could do it in your application. Or you could have an on-insert trigger on event_authors that first checks that the time duration of the new event for this author does not overlap any other events for the same author for different events.

Best Answer

Related Solutions

Sql-server – Representation of circular references constraint

ERD Review Please

Related Question