SQL Schema Entity Design Question

I'm attempting to design a schema for a consignment store's POS system. I have a master item's table and multiple entities that reference it as a foreign key and contain different attributes. The items table contains all of the information that is common to all items, regardless of its type. The entities that reference any given item offer a specific attribute for that given item type. For example, a "split" item needs an asking price, while a store item needs a cost price.

While this design technically works and enforces only one item in the items table, I'd like to开发者_运维百科 be able to maintain that any given item is only referenced by ONE of these entities. The concern is that I don't want a "split" item to also me considered a "store" item accidently. Is there any way to enforce this through schema design?

The ERD is here: http://randywestergren.com/pos-erd.pdf

The tables in question are phppos_items, phppos_items_entity_split, phppos_items_entity_store, etc.

Thanks in advance!

It seems like you're determining whether or not a item is a "split" or a "store" type by which related row exists. Instead, how about adding a type field to the items table? It won't prevent the other related row from actually existing, but it will let you know definitively that it's an error.

Create a secondary table:

typed_items (poorly named)
{
    PK:  item_key - this points to the item
    PK: int item_type_id - this type code specified which 'type' of item, ie: store, split

// optional, depending on your key design:
//     1...n FK: 
//        split_item_id, 
//        store_item_id,
//       etc.., if you reference the items PK in the sub-item-type-detail tables, this
//       isnt nessicary. if you are using a synthetic key on the sub-item-type-detail
//       tables and no key relationship exists you can use this table as a linking table,
//       otherwise its not nessicary
}

Since type items key and the type descriminator id are part of a primary key, you know that your model will not allow an item to belong to 2 different sub-item-type-detail tables.

If you want (paranoid) integrity you can now add a constraint on these type-detail tables that ensures the item's key paired with the constant type descriminator value for that type exists in this table.