CODEWITHSUNDEEP
mysqlsqldatabase-designmysql-workbenchdatabase-schema
Abdall
Abdall

Reputation: 455

Relationship Involving Multiple Subclass Items

I am currently designing an extension to the "Bar Beer Drinkers" database (one that is often used to teach basic SQL Queries).

As part of the design I have created entities that follow the "ISA" pattern, specifically: "Beers," "Food," and "OtherItems" are all "subclasses" of "Item."

The other entity that is involved is the Bars entity.

Between Bars and Items I have a relationship entity called "Sells".

The Schema for these tables are as follows:

Bar[ID(pk), Name, State, Address, Phone, Open, Close]
Beers[Name(pk), Manf]
Food[Name(pk), Manf]
OtherItems[Name(pk), Manf].

As it stands I do not have a table named "Items."

For the Sells table the schema is:

Sells[barID(fk), item(fk), price]

I was hoping to map the PKs from the three tables all into the "item" column in sells. Aka I could put a beer name in it, a food name in it, or an otheritem name in it.

This does not seem to work in MySQLWorkBench as it does not allow me to reference mutltiple foreign keys to the same column.

My potential fix to this problem was to have something like:

Sells[barID{fk), beerItem(fk), foodItem(fk), otherItem(fk), price]

However this would result in every tuple having at least two null values. Is this acceptable? What is the preferred way to include an "ISA" relationship in a relation entity for SQL?

Upvotes: 0

Views: 292

Answers (1)

Courtney Foster
Courtney Foster

Reputation: 383

This question is very similar to this one. The answer is basically the same: No, you cannot have multiple foreign keys pointing to the same column. As Guffa, the answering person for that issue states, "How would you tell where to look for the key?"

You can do it with your proposed solution, although, as you noted, this isn't ideal. It's not normalized at all.

The problem is the multiple tables for the subtypes. The real answer is to have that "items" table that you currently don't have and add a "type" field.

Items{ItemID(pk), ItemName, ItemType}

This way you can then use the ItemID as the foreign key you intended to in the first place. However you choose to go about it is dependent on your needs/goals, etc., but you can't link multiple tables to the same column as foreign keys. There is an ambiguity issue doing that, that the db can't resolve.

Upvotes: 1

Related Questions