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.
With the restrictions you have supplied and if you want to implement this is DBMS, I think you could use a supertype/subtype pattern for the 5 (or more) types of objects and only one additional table for the "object contains objects" list:
-- auxiliary table that has only 5 rows, one for each type
CREATE TABLE types
( level TINYINT NOT NULL PRIMARY KEY
, type_name VARCHAR(10) NOT NULL UNIQUE
) ;
INSERT INTO types (level, type_name)
VALUES (1,'A'), (2,'B'), (3,'C'), (4,'D'), (5,'E') ;
-- this is the supertype
CREATE TABLE objects
( object_id INT NOT NULL UNIQUE
, level TINYINT NOT NULL REFERENCES types (type_id)
, UNIQUE (level, object_id)
) ;
-- and the subtypes
CREATE TABLE a
( object_id INT NOT NULL PRIMARY KEY
, level TINYINT NOT NULL CHECK (level = 1)
--- various columns
, FOREIGN KEY (level, object_id)
REFERENCES objects (level, object_id)
) ;
--- and similarly for the other 4 tables
--- all referencing objects table
and finally the list table, which is simple many to many junction table with only an additional constraint to ensure that an object of higher level can contain only objects of lower levels:
CREATE TABLE contains
( container_level TINYINT NOT NULL
, container_id INT NOT NULL
, item_level TINYINT NOT NULL
, item_id INT NOT NULL
, PRIMARY KEY (container_id, item_id)
, FOREIGN KEY (container_level, container_id)
REFERENCES objects (level, object_id)
, FOREIGN KEY (item_level, item_id)
REFERENCES objects (level, object_id)
, CHECK (container_level > item_level)
) ;
SQLite has CHECK constraints and has recently implemented recursive CTEs, if you want to get the results of an object and all the objects underneath it in one query.
From a fast search, H2 also supports recursive CTEs but Derby does not.
Best Answer
One advantage is that the system can distinguish each thread of conversation. That makes it easier for the computers and for the people. For example, machine analytics will benefit by knowing who replies to whom, and hence who the cliques, influencers and outliers are. People will benefit from being able to segregate each topic of conversation. Responses to direct questions do not get lost in the general traffic. Long-ago posts can be brought back into play without having to explicitly pull context. You can get notification on responses to your messages, rather than just general traffic in the chat room, which is more helpful.
Say I log onto a chat. There are some messages to which I want to reply. One says "Fancy a movie on Tuesday?" the other "Fancy skating on Tuesday?" I reply "yes" and "no." What will happen on Tuesday? I could write "I want to go skating but not see a movie." From my point of view clicking the "reply to" button and typing two single words is much easier and conveys the same information.
As an aside, I'd take statements of an absolute nature about how third-party systems are written with a pinch of salt until they are substantiated by knowledgeable sources.