mardi 3 octobre 2017

How to design a database for types and categories in Laravel?

As the questions states, what is the best way when designing a database for types and categories?

Scenario:

I have x amount of database-tables e.g. users, feedback, facts and countries, and all these tables have a type-attribute. What I've found is that a lot of people tend to just create type-tables for each and one of these. E.g. user_types, feedback_types, fact_types and country_types.

I'm currently working on a project where I don't want to create a bunch of extra tables just to handle their individual types. Therefore I'm trying to come up with a database-design-solution that fits all tables.

My best thought of solution:

At first I thought I might just create a polymorphic table that has id, type_id, typable_id and typable_type and a types table. Then i figured that I have to specify in the types table which type-attribute belongs to which table. Then it hit me, I can create a self-referencing table where the parent name is the table name.

E.g.

---------------------------------------------
|id | parent_id | name        | description |
---------------------------------------------
| 1 | null      | feedback    | something   |
---------------------------------------------
| 2 | 1         | general     | something   |
---------------------------------------------
| 3 | 1         | bug         | something   |
---------------------------------------------
| 4 | 1         | improvement | something   |
---------------------------------------------
| 5 | null      | countries   | something   |
---------------------------------------------
| 4 | 5         | europe      | something   |
---------------------------------------------
| 4 | 5         | asia        | something   |
---------------------------------------------
| etc...                                    |
---------------------------------------------

Is this a ok design? I'm thinking a lot about the parent names in this table, I haven't seen anyone else use table-names as parents.

If thinking about it in a front-end point of view, it's easier to get the correct types depending on which types you're looking for.

Please give me feedback on this. I'm struggling to find a good design.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire