Same field but different contents

database-design

I'm building a database for a divination system. Just to make it easy, I'll explain it in terms of the i ching.

Every element of a hexagram is a different table. There's one for yin and yang, one for the 3 positions in a trigram, one for the trigrams, one for the hexagrams. There's also a table for the correspondences, which is linked to each of the other tables – correspondences for yin or yang, for the 3 positions, the trigrams, and the hexagrams. There is also a table with commentary, which also links to each of the other tables (except for the correspondence table).

So, the table for the trigrams, for example, might be structured like this:

TriID     
TriName    
Line1                    -FK (YinYangID)
Line2                    -FK (YinYangID)
Line3                    -FK (YinYangID)
TrigramMeaning
Correspondence           -FK (CorrespondencesID)
Commentary               -FK (CommentariesID)

With data like:

 TriID  TriName Line1 Line2 Line3 TriMeaning Correspondence  Commentary
 1      Qian    Yin   Yin   Yin   Receptive  1               1
 2      Kun     Yang  Yang  Yang  Generation 2               2

The Correspondence table looks something like this

 CorrID    Person    Season     Color     Animal    Number     Element
 1         Father    Winter     Blue      Bear      3          Metal
 2         Mother    Summer     Red       Deer      6          Earth

(I know that all my I Ching data's wrong…)

The idea is that the same correspondences are required for every table, so the correspondence table would have an entry for each of the other tables. CorrespondencesIDs 1 through 4 might apply to the YinYang table, for example (Old and New Yin, old and new Yang), and then 5 through 13 to each of the trigrams, and so on.

It seems like this would work just right. On the other hand, it feels intuitively like I'm doing something wrong and breaking some rules. Since every entry on the correspondence table will be different, it seems like there should maybe just be similar entries on every table:

TrigramPerson
TrigramSeason
TrigramColor
TrigramAnimal

and then

HexagramPerson
HexagramSeason
HexagramColor

and so on.

What is the smart / best way to structure the database? Apologies if this is a dumb question – I'm new to database design, and some of these concepts still confuse the heck out of me.

Best Answer

I had to re-read your question a few times to get the sense of what you are trying to do. What I suggest is something I call a class-based multi-multi join. In some db's like PostgreSQL, you can then do some advanced referential integrity checks on the joins, but that's outside my answer.

suppose you have a table:

CREATE TABLE correspondence_class (
     id int not null unique, -- hand-assigned
     label text primary key, -- hand-assigned, don't want duplicate labels
);

Then we can:

INSERT INTO correspondence_class (id, label) 
VALUES ('1', 'person'), (2, 'color'), (3, 'season'), -- and so forth

From there we can then have two more tables:

CREATE TABLE trigram_assoc (
   trigram_id int references trigram(id),
   c_class_id int references correspondence_class(id),
   correspondence text not null,
   PRIMARY KEY (trigram_id, c_class_id)
 );

you would then do the same for hexagrams, lines, etc. In PostgreSQL, you could change your primary key to include correspondence too, and uniquely index only those correspondences attached to some correspondence classes. You could also have deferrable constraint triggers that could be used to ensure that all relevant associations were populated, but how exactly to do this on other dbs is a good question.

Related Question