database - Should I make another table that references unique IDs or just add a unique ID columns in Ruby on Rails?

Question

I am creating a social app that consists of the typical community feed like Instagram. Let's pretend it's an app for users to show off their houses. I have 5 tables/models that are made for separate concepts. An example would be, these made up names - "Food", "House", "Electronic", "Person", "Car".

The issue is we decided to have each of these displayed in our Community tab. Furthermore, if a user wants to take a picture of one of these items, there has to be 5 separate checks of logic. So the frontend has to check 5 different endpoints, essentially. Which can be a bit redundant and produce a lot of spaghetti code.

I found two solutions. Both deal with using a UUID so the frontend has to only hit one endpoint.

1. Add a "UUID" column to all 5, and create an endpoint that searches through the 5 tables. The issue is if I have the search to start on table "Car" every time, and that table has way more entries than the table I'm looking for - "Food", the query would consistently be slow every time a user searches through the "Food" table.

2. Create a joins table that has a UUID column and has reference to the other tables. That way when querying the DB will not have to jump from table to table. I think this is the best way, but wanted to get other people's opinions before I make a decision.

question from:https://stackoverflow.com/questions/65882044/should-i-make-another-table-that-references-unique-ids-or-just-add-a-unique-id-c

Answer 1

You can add CommunityHub model which will store 5 nullable id's like food_id; car_id; electronics_id and etc; return that entities + preload cars/foods/electronics

In that case you can return values as

{
  community_hub: {
    id: 1,
    food: { food_data },
    car: nil
  }
}

Hint: also you can add custom check to the db, which can provide consistence check with 1 foreign_key per record, so there are no multiple keys in one record.

Smth like

ALTER TABLE community_hubs ADD CONSTRAINT community_hub_fk_check_constraint
        CHECK (
          (
            (mosque_id IS NOT NULL)::integer +
            (house_id IS NOT NULL)::integer +
            (food_id IS NOT NULL)::integer
          ) = 1
        );