You could formalize the approach that you're already using and make it more efficient to both maintain and query. Consider an ERD like so:
Note that all of your hierarchy is flattened into a single set of columns. There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent. I've also included left and right visitation numbers which would really help you with your price lookups. Lastly there is a Level
column to store the rank within the hierarchy.
If you're not familiar with visitation numbers please have a look at my answer to this question, where I go into quite a bit of detail.
So why is this better than what you're doing now?
You only track each item in the hierarchy once. Instead of having
4001 show up in the channel column many, many times, you just have
one record with 4001.
The structure is flexible and allows for insertion of new levels at a later time. It also allows for uneven trees, say for example ZONE 3001 is subdivided into three SUBZONEs and that cities are under the subzones there (but go direct to zone outside of 3001).
All of your prices are in exactly one place (which doesn't need any reading logic that goes outside of normal SQL queries. Instead of having to have logic that joins a price to different Entity tables depending on what range the EntityID is in, you have one consistent price retrieval query.
You don't need coded meaning in your identifiers so you won't run into trouble when you get too many entities at any one level, thus breaking your coding scheme.
The price retrieval query is dead simple:
-- Look up the left and right numbers for the store in question:
declare @StoreLeft int
declare @StoreRight int
select @StoreLeft = H.left, @StoreRight = H.right
from HIERARCHY H where H.ID = @DesiredStoreId
-- Look up the lowest level (most granular) price for this store:
select TOP 1
P.price_amt
, P.currency
from PRICE P
inner join HIERARCHY H
on H.Left <= @StoreLeft and H.right >= @StoreRight
inner join ARTICLE A
on P.article_id = A.id
where A.id = @TheArticleInQuestion
order by H.Level ASC -- ASC or DESC depends on how you count levels.
Another way (without Nulls and without cycles in the FOREIGN KEY
relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE
constraint on TableB
.
@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id
columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:
Parent
ParentID INT NOT NULL PRIMARY KEY
Child
ChildID INT NOT NULL PRIMARY KEY
ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
UNIQUE (ParentID, ChildID)
FavoriteChild
ParentID INT NOT NULL PRIMARY KEY
ChildID INT NOT NULL
FOREIGN KEY (ParentID, ChildID)
REFERENCES Child (ParentID, ChildID)
In SQL-Server (that you are using), you also have the option of the IsFavorite
bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:
Parent
ParentID INT NOT NULL PRIMARY KEY
Child
ChildID INT NOT NULL PRIMARY KEY
ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
IsFavorite BIT NOT NULL
CREATE UNIQUE INDEX is_FavoriteChild
ON Child (ParentID)
WHERE IsFavorite = 1 ;
And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.
Read a quite old article: SQL By Design: The Circular Reference
When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?
In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL
but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.
Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.
Best Answer
This is a typical 1-to-n relationship: one parent store is related to several child stores.
You don't need separate junction tables to model such a relationship, and it is best to avoid unnecessary tables.
You'd add a foreign key column
parent_store_id
to thestore
table that points to the parent store. If there is no parent store, the column containsNULL
.