Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
@graidan: I suggest you edit the question and add the existing structure of all your tables. And even better a few rows of data of each table. –  ypercube May 18 '12 at 11:55
    
Actually, I gave the above example because it's almost identical to the structure of my database. Adding in 50 fields from 30 other tables is pointless, and a waste of your time. I'm not sure how adding a few rows would make a difference, but I'll do it for one anyway. –  graidan May 18 '12 at 19:21
    
After asking around IRL as well, the answer I've come to is that it's probably simpler and cleaner to incorporate all of the correspondences within each table, i.e. the triColor, triPerson, hexColor, hexPerson model. Since there won't be overlap between each of the correspondences, and every line will be distinct, there's no point in linking them to some other Correspondence table. –  graidan May 21 '12 at 21:43
    
Now, since there won't be an answer here, do I need to do anything else to properly "close" the question? –  graidan May 21 '12 at 21:44
1  
It's not very clear what you are asking, that's why you have no answers yet. –  ypercube May 21 '12 at 21:55
show 2 more comments

migrated from stackoverflow.com May 18 '12 at 11:51

This question came from our site for professional and enthusiast programmers.

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.