Quick Question on Database Design
I'm working on a marketplace similar to Fiverr. I'm Seeking best practices DB design advice. I'm working on the MYSQL DB. In situations where sellers enter related word tags when uploading their products so that buyers can find the products, how should this be entered into the database? option 1: id | product | tags, tags, tags, tags? (one column for tags) or option 2: id | product | tags | tags | tags| tags (each tag has its own column). buyer will be able to search related tags.
"Individual columns for each tag" is a bad design, don't do that.
"one column for all tags" is an okay design. It won't really scale very well, but should be fine for now. Use the mysql JSON datatype so you don't need to do your own serialisation.
The better way of doing this it to have a table for tags names, descriptions, etc. And another table for tags associated with a product. All three tables:
id | product
id | tag_name | tag_description
id | product_id | tag_id
This response is correct, as it doesn't break normalization.
id | product
id | tag_name | tag_description
id | product_id | tag_id
fine for nowleads to pain in the bum later.See top response here, it's well explained: https://stackoverflow.com/questions/3070384/how-to-store-a-list-in-a-column-of-a-database-table
Listen to the
fine for now.If you end the tags string with a comma you can do searches for "<tag phrase>," and
UXwon't matchUX DESIGN. When you want them to match search using "<tag phrase>" (i.e. no comma).Got it. So no commas. It should just be separated by the generic spaces like used in a sentence?
No. Store tags as a comma delimited string in the database and ensure there is a comma on the end if you would like to be able to do "UX" searches that do not match "UX DESIGN" tags. The actual search you do is "UX," which would not match "UX DESIGN,". It's a small point, not going to make or break your product but I remember doing this when storing tags as a string in a single column and it worked well.
Thanks!
Understood. Thanks for clarifying that.
So these are the options as per the comments
Product table
id | product | description (already exist)
Tags table (create)
id | tags
Tags Match table (create)
id | product id | tag id
———————————VS———————————
Product table
id | product | description (already exist)
Tags table (create)
id | product id | tags
Not tags.
Tag. That way you can easily join the product and tag_product table based on tags.
I would create 2 new tables
Job tags would just be the jobId and tagId
Thank you for this.
your solution is a bit old
for me I would use a column tags with json type and a generated column
see more
https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/
Thanks. I’ll check it out