Member Avatar for websponge

I want to have a supplier table, with ID, NAME, CONTACT, PHONE, etc,

In my main table, a record can have more than one of these suppliers, what is the best way to select multiple rows from the supplier table if they relate to a record in the main table?

I was going to have a column in the main table SUPPID and have "1,3,4" as an entry so it selects record 1, 3 and 4 from the supplier table, but obviously this wont work!

any guidance is appreciated.

  • 3 Contributors
  • 3 Replies
  • 247 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by websponge

Dani AI

Generated

As is planning the tables, the right way to model "a product can have many suppliers" is a proper many-to-many relationship instead of storing "1,3,4" in a single column. As suggested, add a link table between products and suppliers; as suggested, prototype the tables/queries in a test DB first. Benefits: atomic data, referential integrity, indexes for fast lookups, and simpler, safe SQL.

Example schema (DDL):

CREATE TABLE suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  contact VARCHAR(200),
  phone VARCHAR(30)
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE product_suppliers (
  product_id INT NOT NULL,
  supplier_id INT NOT NULL,
  PRIMARY KEY (product_id, supplier_id),
  INDEX (supplier_id),
  FOREIGN KEY (product_id)   REFERENCES products(id)  ON DELETE CASCADE,
  FOREIGN KEY (supplier_id)  REFERENCES suppliers(id) ON DELETE CASCADE
);

Insert / retrieval workflow (PHP + PDO example):

$pdo->beginTransaction();
$ins = $pdo->prepare('INSERT IGNORE INTO product_suppliers (product_id, supplier_id) VALUES (?, ?)');
foreach ($supplierIds as $sid) { $ins->execute([$productId, $sid]); }
$pdo->commit();

$q = $pdo->prepare('SELECT s.id, s.name FROM suppliers s
                    JOIN product_suppliers ps ON s.id = ps.supplier_id
                    WHERE ps.product_id = ? ORDER BY s.name');
$q->execute([$productId]);
$suppliers = $q->fetchAll(PDO::FETCH_ASSOC);
$names = implode(', ', array_column($suppliers, 'name'));

Quick tips and cautions: use the composite key (product_id, supplier_id) or a UNIQUE constraint to avoid duplicate links; add extra columns on the link table if the relationship has attributes (price, lead_time, preferred_flag). Avoid CSV lists in one field — they break integrity and make queries slow. If you need aggregated display, either assemble names in application code (shown above) or use the DB’s aggregation features for reporting, but keep the normalized structure underneath.

Recommended Answers

All 3 Replies

Member Avatar for Szabi Zsoldos

First please create a sqlfiddle with your tables and data so we can help you with the query.

Member Avatar for Member #120589

You need a link table, eg.

Supplier:  ID, NAME, CONTACT, PHONE
Product: ProdID, ProdNAME
ProductSuppliers: PSID, ProdID, ID

WHere ProdID and ID in the ProductSuppliers are Foreign Keys. You can force a constraint or not.

To retrieve all the suppliers of a particular product:

SELECT Product.ProdNAME, GROUP_CONCAT(Supplier.NAME) 
    FROM ProductSuppliers AS PS 
        INNER JOIN Product 
            ON Product.ProdID = PS.ProdID 
        INNER JOIN Supplier 
            ON Supplier.ID = PS.SupplierID 
    WHERE Product.ProdID = $product_id 
        GROUP BY Product.ProdID

Off top of my head - so could be wrong.

Member Avatar for websponge

Szabi, this is at planning stage, I havent created the tables yet.

Im familiar with joins, the issue is, how can i have a entry that relate to 2 or 3 records from another table, one product may have more than 1 supplier, so I need to put an entry in the product row that says "I use supplier 2,5 and 6"

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.