MySQL table structure question

Question

How should my mysql table look like when I allow a user to enter there post in multiple categories. Should it look like the below mysql table in example 1 or in example 2 or something different?

EXAMPLE 1

CREATE TABLE ac (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   categoryID INT UNSIGNED NOT NULL,
   articleID INT UNSIGNED NOT NULL,
   dateCREAT DATETIME NOT NULL,
   PRIMARY KEY (id)
);

EXAMPLE 2

CREATE TABLE ac (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   categoryID INT UNSIGNED NOT NULL,
   articleID INT UNSIGNED NOT NULL,
   dateCREAT DATETIME NOT NULL,
   PRIMARY KEY (id)
   UNIQUE KEY (categoryID, articleID)
);

Answer 1

Assuming the ac table is your junction table, you probably don't need the surrogate key id in there.

You can then use a composite primary key to enforce the constraint where the same article cannot be posted twice to the same category, instead of using a unique constraint:

CREATE TABLE ac (
   categoryID   INT UNSIGNED NOT NULL,
   articleID    INT UNSIGNED NOT NULL,
   dateCREAT    DATETIME NOT NULL,
   PRIMARY KEY  (categoryID, articleID)
);

If your application requires the surrogate key id, then your second example would be the way to go. The unique constraint would not allow the same article to be posted to the same category, but would allow any particular article to be posted to more than one category.

Answer 2

I am not quite sure I understand your table setup from the description.

Assu that you have an article table and a category table and you want to map one article to multiple categories then you normally would have an intermediate table with just articleID and categoryID.

e.g. sth like

CREATE TABLE postCategoryMappingc (
    categoryID INT UNSIGNED NOT NULL,
    articleID INT UNSIGNED NOT NULL,
    PRIMARY KEY (categoryID,articleID)
); 

Not sure what you needed the dateCREAT field for but I could go in there as well if absolutely needed.

Answer 3

CREATE TABLE ac (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
    categoryID INT UNSIGNED NOT NULL, 
    articleID INT UNSIGNED NOT NULL, 
    dateCREAT DATETIME NOT NULL, 
    PRIMARY KEY (id),
    UNIQUE KEY (articleID,dateCREAT),
    UNIQUE KEY (categoryID, articleID,dateCREAT)
); 

This will produce bigger indexes but much better query times.

Keeping id is a good idea since other tables may reference ac using just one field.

One field lookups are more efficient.