How do I enter into a ternary relationship?

Asked

Viewed 1,533 times

0

I am creating this model however I do not know if it is correct. I have basic knowledge in database.

How do I enter into a ternary relationship that happens in Supplier - Suppliers_has_product - Product?

DB

mysql sql database relational-model

  • How do you want to enter this data? Do you have an application layer (and if so, in which language) or do you want/need to do this only via SQL/stored fornecedor and produto are simple integers (which you assign) or auto-increment integers (eg.: serial)?

  • I will use PHP, the Ids I set to auto-increment.

1 answer

1


Your relationship does seem right. You register suppliers, products, and when you want to associate a supplier with a product (in a many-to-many ratio) you use your Ids in the intersection table (fornecedor_has_produto).

If you don’t know the Ids, you’ve just entered a vendor and/or product and want to get the bank’s self-assigned ID, use the function LAST_INSERT_ID. Typically, the library you are using to connect to the database (e.g..: PDO, mysqli) will have a function to do this for you (since it is a common feature).

An example (simplified) using PDO:

/* Conecta ao banco de dados */
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);

/* Insere um fornecedor */
$stmt = $dbh->prepare("insert into fornecedor(nome, cnpj, ...) values(:nome, :cnpj, ...);");
$stmt.bindParam(":nome", $nome_do_fornecedor);
$stmt.bindParam(":cnpj", $cnpj_do_fornecedor);
...
$stmt->execute();

/* Obtém o ID do fornecedor inserido */
$id_fornecedor = $dbh->lastInsertId()

/* Insere um produto */
...

/* Obtém o ID do produto inserido */
$id_produto = $dbh->lastInsertId()

/* Relaciona o fornecedor com o produto */
$stmt = $dbh->prepare("insert into fornecedor_has_produto(fornecedor_id, produto_id) values(:fornecedor, :produto);");
$stmt.bindParam(":fornecedor", $id_fornecedor, PDO::PARAM_INT);
$stmt.bindParam(":produto", $id_produto, PDO::PARAM_INT);
$stmt->execute();

(I don’t have practical experience with PHP, I set this example from other online examples, but it must have given to demonstrate what needs to be done. If you want an example with mysqli instead, I can do.)

  • Thanks I’ll do some tests here.

Browser other questions tagged mysql sql database relational-model

You are not signed in. Login or sign up in order to post.