How would you structure this in a database?

If you just need to display this information, and you don't need your users to be able to change the data, I'd strongly consider not putting it in a database. It's a small amount of data that could easily be kept in memory. You could either hard-code it directly in your programming language, or load it once from a file (I'd probably format it as JSON, but there are many options) when your app/server starts. This would be faster and have fewer moving parts than loading it from a database as needed.

If you do need a database, I can see cases where either of the proposed schemas could have advantages.

I'm not an expert in databases by any means. In my experience, DB design is VERY dependent on your use case. Questions you might want to ask yourself:

1. What key values do your business logic mostly query off ? Is it the zodiac name? Maybe the percentage? or the Compatibility type (harmonious, nothing in common, etc)?
2. Are you going to modify or add some variables to the data? maybe add more compatibility type ?
3. And more...

If I were in your shoes, here's what I'd do:

The table will have 12 columns:

1. ID (unique ID for the row, usually just uuid or serial)
2. first_zodiac (the zodiac sign, text values)
3. second_zodiac (the zodiac sign, text values)
4. (column 4-11)Compatibility type (boolean values)
5. Percentage value (float values).

Couple of things to watch out:

1. Make sure there's no duplicate information, in other words, there should be no (pisces, taurus) and (taurus, pisces) in the table because they essentially points to the same cell in the table.

Why I picked this design:

1. You can easily add more compatibilty type by adding columns
2. You can query based on ANY information in the table, for example if you want to see which pair has percentage value more than 50% and has compatibility type of harmonious, you can easily do SELECT (first_zodiac, second_zodiac) FROM table WHERE percentage_value > 0.5 AND harmonious == t;
3. Updating the table is really easy since every cell of the table can be deduced from second and third column (first_zodiac, second_zodiac).

Hope this helps!

• Albert

Zodiac can probably just be an ENum since you have a constant non changing and short list

Relationship table

Zodiac1
Zodiac2
(Primary key is both together)

The rest of you metadata
%match
8bollean fields for the described properties of they are constant

If that's your entire table you probably don't need a db, just a json file or similar

Using the following format:

TABLE (FIELD1, FIELD2, ...)

I'd propose the following structure:

zodiacs (zodiac_id, zodiac_name)
compatibility_flags (compatibility_flag_id, name)
zodiac_compatibility (zodiac_compatibility_id, zodiac_id1, zodiac_id2, compatibility)
zodiac_compatibility_flags (zodiac_compatibility_id, compatibility_flag_id)

Example table rows:

zodiacs: (0, "Aries)
compatibility_flags: (3, "Highly volatile")
zodiac_compatibility: (1, 0, 0, 0.5)
zodiac_compatibility_flags: (1, 3)