2
8 Comments

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?

https://bit.ly/2PPlqE3

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

      This is a good solution!
      And if you make a query to the table through the frontend,
      where the order of entering the two search fields is not fixed, you can use:
      SELECT * FROM TABLE WHERE (FIRST_ZODIAC=:PARAM1 AND SECOND_ZODIAC=:PARAM2) OR (FIRST_ZODIAC=:PARAM2 AND SECOND_ZODIAC=:PARAM1)

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

    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

  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
How I grew a side project to 100k Unique Visitors in 7 days with 0 audience 49 comments Competing with Product Hunt: a month later 33 comments Why do you hate marketing? 29 comments My Top 20 Free Tools That I Use Everyday as an Indie Hacker 17 comments $15k revenues in <4 months as a solopreneur 14 comments Use Your Product 13 comments