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