So I have 3 main model tables in my Laravel project for historical data collection in may database:
- Saints
- Shrines
- Events
I want to add tags to each instance of a model to relate to properties so they can be compared. I can do this with a 'tags' table and then possibly 3 extra pivot tables (saint_tag, shrine_tag, event_tag) with the corresponding id's.
However, I wanted to group tags in each model, particularly a Shrine. So tags will be related to a specific question/ field on each model which may consist of several groups of tags, which for a Shrine could be:
Tag Groups
- Materials: stone, gold, silver
- Type: sculpture, hand-made
- Showing: people, adults, children, royalty, alive
- Saint shown in the shrine: alive, gesturing, sadness
This is a basic example, but as you can see, alive appears in two groups withing the same model and it is important to be able to distinguish which group it is in when comparing data.
I don't want to just store the tags as a string array for each field so that I'm not just comparing strings for instances. Mostly so that I can suggest tags to users inputting data from the tags table and should help with consistency.
So how would it be best to set this up in a database? With a separate column in the pivot table like 'tag_group' maybe? So shrine_tag could have a primary key of [tag_id, shrine_id, tag_group] possibly?
Also, Ideally I like to be able to scale easily, so I can add different models to the system without lots of additional tables. So would it be a good idea to have a single pivot table for all models for their tags and groups with a 4-way primary key like [tag_id, tag_group, model_id, model_type]. That way I can added new model types without new pivot tables? Or is that considered bad practise?
Thanks
via StuckInSpace
