CODEWITHSUNDEEP
mysqldatabasedata-structures
Rosamunda
Rosamunda

Reputation: 14980

Two examples of a mySQL database structure, which one is more efficient and preserves data integrity?

I'm creating a mySQL database for a small blog. This blog will have articles of different "types", like "public interest", "DIY", etc.

My question is about how to organize the database structure: should I create a table for the articles, a table for the types, and a third table that connect the two of them? Or should I just create the first two tables and add a field in the articles table that points out to the id number of the types table?

Option 1:

CREATE TABLE articles(
    id int unsigned not null auto_increment primary key,
    title varchar(300) NULL,
    body TEXT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE articleType(
    id int unsigned not null auto_increment primary key,
    name char(200) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `articleType` (`name`) VALUES
('public interest'),
('DIY')

CREATE TABLE articlesArticleType (
    ID int unsigned not null auto_increment primary key,
    typeID int not null,
    articleID int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Option 2:

CREATE TABLE articles(
    id int unsigned not null auto_increment primary key,
    title varchar(300) NULL,
    body TEXT NULL,
    articleType int NOT NULL DEFAULT 1
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE articleType(
    id int unsigned not null auto_increment primary key,
    name char(200) NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `articleType` (`nombre`) VALUES
('public interest'),
('DIY')

In the second case I just need two tables. Which way is more efficient and preserves data integrity?

Upvotes: 1

Views: 73

Answers (1)

Simrandeep Singh
Simrandeep Singh

Reputation: 547

First and foremost it is important to decide on the cardinality of relationship between the 2 tables - Articles and Types as it will influence the choice of the tables structure. Broadly there are 3 cardinalities possible:

  1. One to One
  2. One to Many
  3. Many to Many

Option 1 will satisfy One to Many and Many to Many cardinalities while Option 2 will satisfy One to One cardinality.

Upvotes: 2

Related Questions