How would you structure this in a database?

I am wondering how someone experienced in the field of databases would structure the information in this table(matrix) in a DB?


PS: I am currently learning some SQL. Any ideas and opinions more than welcome.

  1. 2

    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.

    1. 1

      I totally agree that a JSON can do the job. I also thought about AirTable or GoogleSheet but currently I want to explore a little bit the databases so even if it's not 100% necessary I will stick to this approach :) Ty for your comment.

  2. 2

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

      Thank you, Albert. This definitely help me a lot.
      The business will be very simple:

      2 Drop downs containing all the zodiac signs.
      The user chooses his/her sign and the one to be matched and I just output that compatibility info.

      Thx again for your detailed answer.

  3. 1

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

    Relationship table

    (Primary key is both together)

    The rest of you metadata
    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

  4. 1

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

      This looks a little bit more complex and implies more tables but I like it.
      I will keep in mind your example.

      Thx for the answer :)

Trending on Indie Hackers
Link to your product & tell IH how you came up with your idea 97 comments Share your product or landing page, and I'll give you product design advice 34 comments Can you try my side project? I'm looking for some feedback 🙂 24 comments Does anyone actually use productivity software? Which one? 22 comments Copywriting Examples — The world's best copy. In one place. 13 comments What do you consider 'Idea validated'? 6 comments