How to make one-to-many foreign keys using PHP

Question

I have a table with 3 columns:

  noticias   | categorias | tags
-----------------------------------------------
     id      |     id     | tag_a
noticia_nome |  tag_nome  | tag_b
 noticia_id  |            |  ...
             |            | tag_j (10 no total)

The column tags has 10 tags, and I have news that will use 4 tags as I make a foreign key (Foreign key), to connect the noticia_id with 4 tags?

Answer 1

You are confusing the concept of column with the concept of table.

Watch this table of yours:

  noticias   | categorias | tags
-----------------------------------------------
     id      |     id     | tag_a
noticia_nome |  tag_nome  | tag_b
 noticia_id  |            | tag_c
             |            | tag_d
             |            | tag_e
             |            | tag_f
             |            | tag_g
             |            | tag_h
             |            | tag_i
             |            | tag_j

In this table, lines are not things that have much logical meaning. What you want is to work with three different tables.

Let’s see how to organize these tables:

• A news story is something that has a name and an id. Two different news stories are not allowed to have the same name.

• A tag is something that has a name and an id. Two different tags are not allowed to have the same name.

• You want to categorize news by tagging it. A news story can have several different tags. The same tag can appear in several different news. That is, this is a relationship many-to-many, or also called M-to-N (or N-to-N). It is not a one-to-many relationship as it is in your question. It is also noted that the same tag cannot appear more than once in the same news and not even the same news can appear more than once in the same tag.

So your tables look like this:

CREATE TABLE noticia (
    id int(11) NOT NULL AUTO_INCREMENT,
    nome varchar(50) NOT NULL,
    PRIMARY KEY pk_noticia (id),
    UNIQUE KEY noticia_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tag (
    id int(11) NOT NULL AUTO_INCREMENT,
    nome varchar(50) NOT NULL,
    PRIMARY KEY pk_tag (id),
    UNIQUE KEY tag_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE noticia_tag (
    id_noticia int(11) NOT NULL,
    id_tag int(11) NOT NULL,
    PRIMARY KEY pk_noticia_tag (id_noticia, id_tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE noticia_tag ADD CONSTRAINT fk_noticia_tag_noticia FOREIGN KEY (noticia_id) REFERENCES noticia(id);
ALTER TABLE noticia_tag ADD CONSTRAINT fk_noticia_tag_tag FOREIGN KEY (tag_id) REFERENCES tag(id);
CREATE INDEX idx_noticia_tag_noticia ON noticia_tag (id_noticia);
CREATE INDEX idx_noticia_tag_tag ON noticia_tag (id_tag);

To understand this script, let’s make some observations:

• The instruction CREATE TABLE is responsible for creating a table by specifying in it, within the parentheses, which are the columns.

• Each column in the instruction CREATE TABLE starts with the column name and type (here we use the types int(11) and varchar(50)). After the column type, modifiers can be added. The modifiers we use here are NOT NULL and AUTO_INCREMENT.

• The AUTO_INCREMENT says that the column value will be filled automatically by the database when an insertion occurs.

• The NOT NULL says that the column value can never be NULL.

• The lines of the type PRIMARY KEY pk_tabela (id) they say the countryside id is the primary key in each of these tables. In the case of a composite primary key, there is more than one field within these parentheses in the PRIMARY KEY, as in the PRIMARY KEY pk_notica_tag (id_noticia, id_tag).

• On the lines of type PRIMARY KEY, that name that appears between the word KEY and the opening parenthesis is the name of the primary key. You do not need to specify this if you do not want, you can only use PRIMARY KEY (id) if you prefer. However, if you do not give a name to your primary key, the database will give the name PRIMARY automatically.

• The lines of the type UNIQUE KEY have a format similar to that of PRIMARY KEY and serve to specify which sets of columns cannot be repeated even if they are not the primary keys. Each unique key can have a name, and if you do not specify a name, the database will invent a name by itself based on the name of the chosen columns (but not always the invented name will be a good name).

• To list which news has which tags, we use the table noticia_tag. This table contains nothing more than lines that match a news item with a tag. The same news can appear several times, and the same tag as well. However, the same news with the same tag can only appear once in this table, since this is the table’s primary key.

• Mysql has several Engines different, which are different ways of storing tables. For each table we are using the engine Innodb. This is done by using ENGINE=InnoDB after the closed-percentric of each CREATE TABLE. The Mysql have several Engines different.

• To avoid problems of encoding, we use UTF-8 for the encoding of texts. That’s what the DEFAULT CHARSET=utf8 in each instruction CREATE TABLE that is to say.

• The instructions ALTER TABLE add foreign keys. It is possible to add them directly to CREATE TABLE also, but I prefer to do with ALTER TABLE because then you can put all the instructions ALTER TABLE at the end of the database creation script and with this need not worry about the order in which tables are created.

• The instructions CREATE INDEX, as the name already says, create indexes. An index serves to speed up the search in a table, avoiding that it has to be traveled line by line when a search is performed. Two indexes are created in the table pk_notica_tag. One of them indexes by id_tag, and serves to quickly find all news with a certain tag. The other indexes by id_noticia, serving to quickly find all existing tags in a particular news.

Answer 2

Tells your instance of connection to the bank to do this plus replace my data with yours

CREATE TABLE `cidades` (

`codcidade` INT NOT NULL ,
`descricao` VARCHAR( 50 ) NOT NULL

) ENGINE = innodb;

CREATE TABLE `clientes` (

`codcliente` INT PRIMARY KEY ,
`nome` VARCHAR( 50 ) NOT NULL

) ENGINE = innodb;

CREATE TABLE `vendedores` (

`codvendedor` INT PRIMARY KEY ,
`nome` VARCHAR( 50 ) NOT NULL

) ENGINE = innodb;

CREATE TABLE `vendas` (

`codvenda` INT PRIMARY KEY ,
`datavenda` DATE NOT NULL

`codcliente` INT,
`codvendedor` INT

) ENGINE = innodb;


ALTER TABLE `clientes` ADD CONSTRAINT `fk_cidade` FOREIGN KEY ( `codcidade` ) REFERENCES `cidade` ( `codcidade` ) ;

ALTER TABLE `vendas` ADD CONSTRAINT `fk_vendas_codcliente` FOREIGN KEY ( `codcliente` ) REFERENCES `clientes` ( ` codcliente ` ) ;

ALTER TABLE `vendas` ADD CONSTRAINT `fk_vendas_codvendedor` FOREIGN KEY ( `codvendedor` ) REFERENCES `vendedores` ( ` codvendedor ` ) ;