Have Geek Brain

Associate tags with entities, save a tag one or more times?

I have a table with notes that can be associated with zero or more tags, as I would like to do

create table notes (
  id int , -- primary key
  -- other fields
);
create table tagmapping (
 noteid int, -- refers notes.id
 tagid int, -- refers tags.id
 );
create table tags (
  int id, -- primary key
  tagname varchar(255)
);

versus saving the same tag potentially multiple times, as in

create table notes (
  int id, -- primary key
  -- other fields

);

create table bar (
  id id, -- primary key
  tag varchar(255),
  -- other fields
  noteid int -- refers to notes.id, (not unique)
);

What confusion / benefits will I get for the latter approach?

+2


source to share


3 answers


The first is the standard way to implement many-to-many relationships. You have to do this because this is what will least surprise people.

A couple of problems with the second method:



  • You will have to duplicate tag names, which leads to redundancy. This can be a waste of disk space if tag names are long.
  • If you want to rename a tag, you need to update the entire table instead of a single field.
+1


source


For the second approach:



  • Harder to query (for reports etc.) as tags can be potentially erroneous and other things like this
  • Speed. It is faster than a query to an int than a string

  • What if you need to rename a tag? You will have to rename it in each note manually or through a series of queries

  • Saving duplicate data is generally considered bad practice among DBAs and developers. It's best to always follow the normalization rules whenever you can

0


source


The disadvantages of the second solution are partially off when you need to change the tag or add attributes to the tags. Suppose you have a tag named "Foo" and now you want to add an attribute that will allow you to determine if this tag is active or not. In the second approach, there is no means for this. You will need to force the "magic tag names" into the presentation level. Also, you don't have the means to enforce consistent tag naming. Someone could add a tag called "Foo" and another call to "Fu", although they must be the same tag.

0


source






More articles:


All Articles