One to X relationship constrained by the database

[Sgraffite]

Using SQL it is easy to create one to one and one to many relationships by structuring your tables correctly. I wanted a one to X relationship where X was a value stored in the database, but I also wanted the database to enforce that relationship.

For example the game I'm working on has items with X number of sockets. I wanted the database to enforce the maximum number of X gems to be able to be socketed in an item. In this case X is [Item].[GemSlotCount]. The only way I was able to accomplish this was by using a check constraint (using SQL Server 2014).

Here's my database diagram:

The check constrain runs on INSERT and UPDATE of the [ItemGem] table.

From what I read online it seems there may be a possibility of skipping the check constraint on multi-row inserts, but in my limited testing I was not able to bypass the check constraint. Another possible way of doing this would be triggers, but from what I've read a check constraint should be used before a trigger, if possible.

I really have no idea about performance of this implementation, nor do I have experience load testing such. I know I could do this via code, but I prefer the database to maintain it's own integrity.

My questions are:
Is there any known problem(s) with doing the constraint in this manner?
Is a better way known?

 

[Mr.IncrediblyBored]

Your looking to limit how many can be assigned in a one-to-many relationship? So if the limit for item A is 5,there can be up to 5 somethings assigned to A?

I'm not a fan of anything more complicated than foreign keys for establishing constraints in a database. Everything else should be outside the persistence layer -- ie, in code. Is the data coming from user input or a blind import where you have no other way of validating the data?

 

[cabri]

Place code in your interface to the DB to keep track of open connections.

That is where such constraints belong. Implimentation layer

 

[KentState]

Add a slot type table and unique index. The slot type table would have 5 records, one for each slot type.

OR


Is your GemSlot table doing this? If that's the case, then you just need a unique index on the ItemGem table.

CREATE UNIQUE INDEX IDX_ItemGem_Unique on dbo.ItemGem (ItemID, GemSlotID) INCLUDE (ID);
This would only allow you to insert up to five rows per item and the GemSlotID's have to range from 1-5.

I would also suggest not naming the primary key on each table ID as that is confusing.

 

[Sgraffite]

Your looking to limit how many can be assigned in a one-to-many relationship? So if the limit for item A is 5,there can be up to 5 somethings assigned to A?

Yup, that is exactly what this is doing.

I'm not a fan of anything more complicated than foreign keys for establishing constraints in a database. Everything else should be outside the persistence layer -- ie, in code. Is the data coming from user input or a blind import where you have no other way of validating the data?

The data is whatever I make it currently, as it's a game I'm creating. I guess I like it when the database prevents impossible situations as much as it can within reason. This is why I was asking if there are any known problems with doing this as I am not sure myself. I just know that it *appears* to be working in a local testing environment.

Add a slot type table and unique index. The slot type table would have 5 records, one for each slot type.

OR


Is your GemSlot table doing this? If that's the case, then you just need a unique index on the ItemGem table.

Yes the only purpose of GemSlot is to enforce valid GemSlots via the foreign key, as well as enforce a valid GemSlotCount via the foreign key.

CREATE UNIQUE INDEX IDX_ItemGem_Unique on dbo.ItemGem (ItemID, GemSlotID) INCLUDE (ID);
This would only allow you to insert up to five rows per item and the GemSlotID's have to range from 1-5.

I would also suggest not naming the primary key on each table ID as that is confusing.

I have the unique index already to prevent multiple Gems being linked to the same slot on a single Item. My opinion is having the primary key always named ID is less confusing *shrug*.

 

[KentState]

So you don't need the check constraint with the unique index?

 

[Sgraffite]

So you don't need the check constraint with the unique index?

You still do because the check constrain is limiting the one-to-many relationship to a max number of relations based on the value stored in the Item.GemSlotCount field.

 

[KentState]

What I'm saying is that your unique index will enforce that already. If not then it's a matter of adding a table/columns. For example, if you your GemSlot table has 5 rows, you can technically by RI only have up to 5 rows and the unique index constrains them to 5 unique rows.

 

[Sgraffite]

What I'm saying is that your unique index will enforce that already. If not then it's a matter of adding a table/columns. For example, if you your GemSlot table has 5 rows, you can technically by RI only have up to 5 rows and the unique index constrains them to 5 unique rows.

It will only enforce to the maximum number of rows in the GemSlot table though. Not all items will have the max number of slots, most will have less. The check constraint is there to enforce the relationship to limit the item to a max of Item.GemSlotCount number of Gems.

 

[Merad]

You don't need a function for the check constraint. A simple between will do.

ALTER TABLE ItemGem
ADD CONSTRAINT Ck_ItemGem_GemSlotCount CHECK (GemSlotID BETWEEN 0 AND 4);

Edit: Woops, that alone won't be enough. You should probably make that table use a composite primary key that includes GemSlotID.

 

[Sgraffite]

You don't need a function for the check constraint. A simple between will do.

ALTER TABLE ItemGem
ADD CONSTRAINT Ck_ItemGem_GemSlotCount CHECK (GemSlotID BETWEEN 0 AND 4);

Edit: Woops, that alone won't be enough. You should probably make that table use a composite primary key that includes GemSlotID.

I don't understand how add a composite primary key would benefit this situation? Wouldn't the unique constraint on ItemID and GemSlotId accomplish the same thing, minus the goofiness of a composite key?

 

[Merad]

There's what works, and then there's what's correct.

A primary key implies uniqueness, so yeah, the unique constraint has the same effect on this problem. However, if you sit down and map this out in an ER diagram, I think you'll find that ItemGem is a weak entity in an identifying relationship with Item. This means that the primary key from Item becomes part of a composite primary key for ItemGem along with the other column(s) that are necessary to uniquely identify the ItemGem (the slot index). Having an addition "ID" integer column is redundant and unnecessary.

 

[Sgraffite]

There's what works, and then there's what's correct.

A primary key implies uniqueness, so yeah, the unique constraint has the same effect on this problem. However, if you sit down and map this out in an ER diagram, I think you'll find that ItemGem is a weak entity in an identifying relationship with Item. This means that the primary key from Item becomes part of a composite primary key for ItemGem along with the other column(s) that are necessary to uniquely identify the ItemGem (the slot index). Having an addition "ID" integer column is redundant and unnecessary.

I disagree that the ID column is redundant. It allows distinct referencing of a row and is not part of the data the row contains. I've run into problems in the past using composite keys when updating rows, as updating the row may include updating the primary key, in which case the row in question may become ambiguous.

 

[Merad]

I disagree that the ID column is redundant. It allows distinct referencing of a row and is not part of the data the row contains. I've run into problems in the past using composite keys when updating rows, as updating the row may include updating the primary key, in which case the row in question may become ambiguous.

Not to sound like a broken record, but those problems are symptoms of a bad database design. If a column needs to be updated, it probably shouldn't be a primary key.

Composite keys are usually used in the case of weak entities. For example, in a project at work right now, several entities require a history of their changes to be recording. So, for each table XXX, there's a matching XXXHistories table. In the item table, each item does have an ID primary key, as well as a revision number. In the history table, the ID alone isn't enough, so the key is a composite of the ID and revision number.

 

[Ken g6]

I think I understand what you're saying. Sgraffite, I agree, an ID column is useful for foreign keys. Merad, you're not saying that an ID column is redundant; you're saying that having the ID column in the primary key is redundant, right?

 

[brianmanahan]

i would argue that it is easier and better to enforce the limit of one-to-many relationships in the application, not the database

 

[Merad]

I think I understand what you're saying. Sgraffite, I agree, an ID column is useful for foreign keys. Merad, you're not saying that an ID column is redundant; you're saying that having the ID column in the primary key is redundant, right?

I'm saying that the ID is redundant.

A composite primary key says, I need more than one piece of information to identify this entity. Whenever you're referencing the entity you'll have those multiple pieces, and if you go to look at an entity you can't really identify it without all the pieces.

In this case the ItemGems are a weak entity that's identified by an item. An ID makes sense for a "strong entity" that stands on its own and will be referenced throughout the db in foreign keys, but a weak entity is usually pretty closely tied to its identifying entity. Just think about how you'd logically iterate over the gems a player has:

for each item in inventory:
  for slot in 0 to 4:
    do something with gem (item.id, slot)

When you access a gem, you're already operating within the context of an item. The classic example in most db classes is the employee db, where an employee's dependents are weak entities. Talking about dependents on their own isn't very useful. "Here's dependent Jane Doe" vs "here's Jane Doe, dependent of Merad."

Having an ID column in this context isn't going to break your schema, it just isn't needed. Personally (and this isn't a jab at anyone here) I think a lot of software kind of pushes you toward the "just use an ID column PK in every table" approach because it is easier to get a working db that way, even if it causes you grief later due to poor design. Even most people who've had a db class don't come out with a good understanding of how to design a schema from scratch. I TA'd a db class for 3 semesters, I've seen it firsthand.

 

[Mr.IncrediblyBored]

Check constraints are painful to debug and manage. The database's responsibility is to store data and enforce basic data integrity. All business rules like what the OP described should be in application code, not check constraints or triggers.

There are times where a unique ID makes sense instead of a composite primary key. Usually when that composition is then used in another table as part of another composite.

For example item A is associated to Gem 1 and Gem 2. Then that pairing (Gem A->Item 1/2) is assigned to some other construct. Like for example, all sales employees get Bag type X, where identifier X holds the pairing (A->1/2), and all other employees get Bag type Y. There are other times it makes sense too. Since I don't know the usage of the Item->Gem association, I can't suggest changing it.

But what I can suggest is rename the "ID" columns so they are consistant across all tables they are used in.

ITEM TABLE
itemId*
maxGemCount

GEM TABLE
gemId*

ITEM_GEM TABLE
itemGemId*
itemId
gemId

*primary key

 

[Sgraffite]

But what I can suggest is rename the "ID" columns so they are consistant across all tables they are used in.

ITEM TABLE
itemId*
maxGemCount

GEM TABLE
gemId*

ITEM_GEM TABLE
itemGemId*
itemId
gemId

*primary key

Is there any particular reason you suggest this? I purposely did it the other way because I already knew the table name, so I didn't see a need to include it as a prefix to the ID column.

 

[Mr.IncrediblyBored]

The same reason you don't name variables a, b, x, y, etc -- to eliminate ambiguity even if it's obvious. Would your ER diagram be as easy to follow without the arrows connecting everything? Maybe in this simple case.

 

[Sgraffite]

The same reason you don't name variables a, b, x, y, etc -- to eliminate ambiguity even if it's obvious. Would your ER diagram be as easy to follow without the arrows connecting everything? Maybe in this simple case.

You have the table name for context, unlike variables with no context. When you reference Gem.ID and Item.ID it isn't confusing, even though the columns are both named ID.

 

[BoberFett]

You have the table name for context, unlike variables with no context. When you reference Gem.ID and Item.ID it isn't confusing, even though the columns are both named ID.

Until you start using aliases in queries, or even worse views.

select w.ID, uw.ID, g1.ID, g2.ID, g3.ID, g4.ID, g5.ID
from Weapon w join UserWeapon uw on w.ID = uw.WeaponID
join Gems g1 on uw.Gem1ID = g1.ID
join Gems g2 on uw.Gem2ID = g2.ID
join Gems g3 on uw.Gem3ID = g3.ID
join Gems g4 on uw.Gem4ID = g4.ID
join Gems g5 on uw.Gem5ID = g5.ID

Once that's abstracted to a view, you have no idea what's what.

 

[Merad]

Check constraints are painful to debug and manage. The database's responsibility is to store data and enforce basic data integrity. All business rules like what the OP described should be in application code, not check constraints or triggers.

A database that allows itself to be put in an invalid state is plenty painful in itself. There's no reason not to have checks in both places. The application logic should verify that it's doing an insert or update with valid data, so it can prompt the user if there's a problem. The db should verify incoming data as much as possible with checks and other constraints so that it rejects bad data, and so the expectations for the data are explicitly part of the db.

 

[Sgraffite]

Until you start using aliases in queries, or even worse views.

select w.ID, uw.ID, g1.ID, g2.ID, g3.ID, g4.ID, g5.ID
from Weapon w join UserWeapon uw on w.ID = uw.WeaponID
join Gems g1 on uw.Gem1ID = g1.ID
join Gems g2 on uw.Gem2ID = g2.ID
join Gems g3 on uw.Gem3ID = g3.ID
join Gems g4 on uw.Gem4ID = g4.ID
join Gems g5 on uw.Gem5ID = g5.ID

Once that's abstracted to a view, you have no idea what's what.

I understand that, and if that's the biggest downside I'll take it over the alternative. The ORM I'm using does database table mapping to C# objects and I prefer to the syntax Object.Id instead of Object.ClassNameID when referencing the ID of an object.

I purposely didn't make multiple columns in the database for each Gem in the Item table so that I wouldn't need multiple joins to the Gem table to aggregate information. There shouldn't be any need to do what you're doing in your example.

 

[Mr.IncrediblyBored]

A database that allows itself to be put in an invalid state is plenty painful in itself. There's no reason not to have checks in both places. The application logic should verify that it's doing an insert or update with valid data, so it can prompt the user if there's a problem. The db should verify incoming data as much as possible with checks and other constraints so that it rejects bad data, and so the expectations for the data are explicitly part of the db.

Are you going to maintain validation logic in 3 or more places if your application has to support more than one DBMS -- with different syntax, no less? Development tools and support are considerably better for application code. Now you're stuck trying to implement unit tests for each platform.

There are times where you could make an argument for putting business logic into the database, like for data warehousing systems, but even that's debatable. The OP described his app as a "game" so I'm going to assume this isn't one of those times. Dividing responsibility into several application layers is a no-no.