To Prevent the Use of Duplicate Tags in a Database

To prevent the use of duplicate Tags in a database

I have updated my NORMA model to more closely match your diagram. I can see where you've made a few mistakes, but some of them may have been due to my earlier model.

I have updated this model to prevent duplicate tags. It didn't really matter before. But since you want it, here it is (for Postgres):

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

CREATE SCHEMA so;

SET search_path TO SO,"$user",public;

CREATE DOMAIN so.HashedPassword AS 
    BIGINT CONSTRAINT HashedPassword_Unsigned_Chk CHECK (VALUE >= 0);

CREATE TABLE so."User"
(
    USER_ID SERIAL NOT NULL,
    USER_NAME CHARACTER VARYING(50) NOT NULL,
    EMAIL_ADDRESS CHARACTER VARYING(256) NOT NULL,
    HASHED_PASSWORD so.HashedPassword NOT NULL,
    OPEN_ID CHARACTER VARYING(512),
    A_MODERATOR BOOLEAN,
    LOGGED_IN BOOLEAN,
    HAS_BEEN_SENT_A_MODERATOR_MESSAGE BOOLEAN,
    CONSTRAINT User_PK PRIMARY KEY(USER_ID)
);

CREATE TABLE so.Question
(
    QUESTION_ID SERIAL NOT NULL,
    TITLE CHARACTER VARYING(256) NOT NULL,
    WAS_SENT_AT_TIME TIMESTAMP NOT NULL,
    BODY CHARACTER VARYING NOT NULL,
    USER_ID INTEGER NOT NULL,
    FLAGGED_FOR_MODERATOR_REMOVAL BOOLEAN,
    WAS_LAST_CHECKED_BY_MODERATOR_AT_TIME TIMESTAMP,
    CONSTRAINT Question_PK PRIMARY KEY(QUESTION_ID)
);

CREATE TABLE so.Tag
(
    TAG_ID SERIAL NOT NULL,
    TAG_NAME CHARACTER VARYING(20) NOT NULL,
    CONSTRAINT Tag_PK PRIMARY KEY(TAG_ID),
    CONSTRAINT Tag_UC UNIQUE(TAG_NAME)
);

CREATE TABLE so.QuestionTaggedTag
(
    QUESTION_ID INTEGER NOT NULL,
    TAG_ID INTEGER NOT NULL,
    CONSTRAINT QuestionTaggedTag_PK PRIMARY KEY(QUESTION_ID, TAG_ID)
);

CREATE TABLE so.Answer
(
    ANSWER_ID SERIAL NOT NULL,
    BODY CHARACTER VARYING NOT NULL,
    USER_ID INTEGER NOT NULL,
    QUESTION_ID INTEGER NOT NULL,
    CONSTRAINT Answer_PK PRIMARY KEY(ANSWER_ID)
);

ALTER TABLE so.Question 
    ADD CONSTRAINT Question_FK FOREIGN KEY (USER_ID) 
    REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.QuestionTaggedTag 
    ADD CONSTRAINT QuestionTaggedTag_FK1 FOREIGN KEY (QUESTION_ID) 
    REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.QuestionTaggedTag 
    ADD CONSTRAINT QuestionTaggedTag_FK2 FOREIGN KEY (TAG_ID) 
    REFERENCES so.Tag (TAG_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.Answer 
    ADD CONSTRAINT Answer_FK1 FOREIGN KEY (USER_ID) 
    REFERENCES so."User" (USER_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE so.Answer 
    ADD CONSTRAINT Answer_FK2 FOREIGN KEY (QUESTION_ID) 
    REFERENCES so.Question (QUESTION_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

COMMIT WORK;

Note that there is now a separate Tag table with TAG_ID as the primary key. TAG_NAME is a separate column with a uniqueness constraint over it, preventing duplicate tags. The QuestionTaggedTag table now has (QUESTION_ID, TAG_ID), which is also its primary key.

I hope I didn't go too far in answering this, but when I tried to write smaller answers, I kept having to untangle my earlier answers, and it seemed simpler just to post this.

How can I prevent duplicate db queries when using Django's inclusion tag?

You can call your tag in base.html and save the data that it returns and then pass it to other sub templates that might need that data.

But i don't think you can use inclusion_tag anymore. You probably need to use something like simple_tag. Here is an example:

simple_tag:

@register.simple_tag
def widget(some_data):
    return Foo.objects.all()

base template:

# You can access the result as foo_objs
{% widget some_data as foo_objs %}

# pass the data to other templates:
{% include "menu.html" with foo_objs=foo_objs %}
{% include "footer.html" with foo_objs=foo_objs %}

Now the widget function called once and data can be passed around.

Django docs on include

prevent duplicate entries in database

If you want to handle at database level

• you can go with Unique Constraint on Multiple columns
• or you can create a composite primary key like:

CREATE TABLE TableName (
  Field1 varchar(20),
  Field2  INT,
  PRIMARY KEY (Field1, Field2))

Furthermore if you wish to do this on Code level

• if you are using Entity framework, you can declare your class/Entity as IEquatable
  and implement equals functionality in class:

public bool Equals(ClassOrEntity other)
        {
            if (other == null)
                return false;

            if ((this.Field1 == other.Field1)
                && (this.Field2 == other.Field2)
                && (this.Field3 == other.Field3))
                return true;

            return false;
    }

and while inserting values in DB using EF you can check if such value already exists in DB or not:

var ExistingEntries = await context.Entity.Where(p => p.Field1 == Obj.Field1 && p.Field2 == Obj.Field2).FirstOrDefaultAsync();

if(!ExistingEntries.Equals(Obj)
     return error

PHP: prevent duplicated tags in Mysql

1. Put a UNIQUE constraint on the name column.

2. Use INSERT IGNORE so that inserting a column where name already exists simply skips that row. eg:

   INSERT IGNORE INTO tags (name) VALUES ('Linux'), ('OpenBSD'), ('Test');
   

3. Because you have different casings in your data [eg: linux vs Linux] you either need to make sure that you are using a case-insensitive coallation in your database like utf8_latin1_ci [ci stands for case-insensitive] or be sure to use strtolower() on your data prior to inserting it.

How to prevent duplicate entries but allow duplicates in one column MYSQL?

SELECT 
email,
restaurant-id
FROM 
your_table
GROUP BY 
email,
restaurant-id

has_and_belongs_to_many, avoiding dupes in the join table

I worked around this by creating a before_save filter that fixes stuff up.

class Post < ActiveRecord::Base 
   has_and_belongs_to_many :tags
   before_save :fix_tags

   def tag_list= (tag_list) 
      self.tags.clear 
      tag_list.strip.split(' ').each do 
        self.tags.build(:name => tag) 
      end
   end  

    def fix_tags
      if self.tags.loaded?
        new_tags = [] 
        self.tags.each do |tag|
          if existing = Tag.find_by_name(tag.name) 
            new_tags << existing
          else 
            new_tags << tag
          end   
        end

        self.tags = new_tags 
      end
    end

end

It could be slightly optimised to work in batches with the tags, also it may need some slightly better transactional support.

---

Related Topics