0

this is a programming challenge I've undertaken to learn some MySQL fundamentals and I can't figure out how to implement a one-to-many relationship in the manner I need. See the below code:

CREATE TABLE Product (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price FLOAT(7,7) NOT NULL,
    needed_for_bundle INT(2) NOT NULL
)

CREATE TABLE Bundle ( 
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    price FLOAT(7,7)
)

I can't find anywhere how to do this or I'm just not understanding the examples I've found that were similar. If these were python objects, I would have a list of product IDs in the bundle table/object that reference the 3-4 items in the bundle. I know this is a one-to-many relationship but I'm not understanding lexically and theoretically how it would work in MySQL.

Help would be much appreciated. Thank you all.

4
  • Can a product be in more than one bundle? – Willem Renzema Jul 26 '19 at 3:31
  • No. Products will only belong to one bundle. – leaustinwile Jul 26 '19 at 3:36
  • Strange. Product can need for bundle but there is no any field which states what bundle the product is referenced if needed. And backward no data in bundle which product it is referenced to. Your structure is partial it seems. – Akina Jul 26 '19 at 4:12
  • Well currently we're designing it as if there was only one bundle and 4 products. I was trying to figure out how to reference the products that are in bundle from inside the bundle table. – leaustinwile Jul 26 '19 at 6:03
0
CREATE TABLE Product (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    bundle_id  SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    INDEX(bundle_id),
    INDEX(name)
)

CREATE TABLE Bundle ( 
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    price DECIMAL(8,2) NOT NULL,
    PRIMARY KEY(id)
)

Notes:

  • The Product.bundle_id links to Bundle.id; see example query, below.
  • The (6) means nothing on INTs, toss it. And use suitably sized variant of INT.
  • UNSIGNED -- good. (Most numbers in schema are unsigned.)
  • Do not use FLOAT for monetary values, use DECIMAL.
  • Do not use (m,n) on FLOAT or DOUBLE (but do use it on DECIMAL).

List all products:

SELECT  p.name  AS 'Product Name',
        p.price  AS 'Product price',
        b.price  AS 'Bundle price'
    FROM Product AS p
    JOIN Bundle AS b  ON b.id = p.bundle_id;

List all bundles, with a list of product names in each:

SELECT  b.price  AS 'Bundle price',
        GROUP_CONCAT(p.name SEPARATOR ', ') AS products
    FROM Product AS p
    JOIN Bundle AS b  ON b.id = p.bundle_id
    GROUP BY b.bundle_id;

Notice how Bundle's PRIMARY KEY(id) together with bundle_id essentially define a "1:many" relationship, which is exercised via the JOIN.

Later, you can learn about FOREIGN KEYs and TRIGGERs and other sophisticated things.

1

Setting the column "needed_for_bundle" as a Foreign Key that references the Primary Key of the table Bundle would mean that there's a one-to-many relationship between the two tables. Each product has to belong to one bundle, and one bundle can be composed of one or * product(s).

So first, needed_for_bundle should be the same type as the Primary key of Bundle table which is INT(6).

After changing that you can apply this code, for the foreign key constraint + index:

ALTER TABLE `Product` 
ADD FOREIGN KEY (`needed_for_bundle`) 
    REFERENCES `Bundle` (`id`) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE;
CREATE INDEX `fk-id_bundle-Product-Bundle` 
    ON `Product` (`needed_for_bundle`) USING BTREE;
5
  • "Each product has to belong to one bundle, and one bundle can be composed of one or * product(s)." First, thank you for your answer. Let me clarify the exact requirement for simplicity sake because I don't seem to understand your answer (albeit probably correct). There is only 1 bundle. That bundle consists of all of the products (there are only 4). They are static entries. Is there a simpler way to do something like this? I've read about intersection tables but I don't understand them. – leaustinwile Jul 26 '19 at 6:17
  • So basically in your example, in the 'Bundle' table there will be only one line with id = 1, then there will be 4 lines in the 'Product' table where their id would be 1,2,3,4. and for all these 'Product' lines, they would have the same value for 'needed_for_bundle' = 1 (which refers to the bundle id=1). a query to illustrate that: SELECT * FROM Product a INNER JOIN Bundle b ON a.needed_for_bundle = b.id WHERE b.id=1 --> it will show all the products of bundle 1 – LookingForIdeas Jul 26 '19 at 8:09
  • I don't know if I'm clear. I suggest you to read more about Foreign Key concept! – LookingForIdeas Jul 26 '19 at 8:10
  • You should add first the index and then the FK. Or in a single statement. Otherwise you will end with 2 identical indexes. – ypercubeᵀᴹ Jul 26 '19 at 14:32
  • Otherwise +1 from me. – ypercubeᵀᴹ Jul 26 '19 at 14:33
0

only one bundle and 4 products ... to reference the products that are in bundle from inside the bundle table.

Disclaimer. The solution described below is BAD PRACTICE. But if a man wants to shoot himself, who am I to stop him?


If you want to create "backward one-to-many relation", and refer from "one" side to "many" side, you can emulate the relation using referential field where all N-side records identifiers are stored in some solid format.

This solid format can be:

  • CSV - IDs are stored in one field of string type (long varchar or text/blob) separated by some char (default is comma, but you can use some another). Order can be stored if needed.

  • JSON - IDs are stored in JSON array or object. If you'll use object you will need to generate some key values (it can be identity values, for example), and use key values for order storing. In array the order cannot be stored and will be lost during save data into the record's field.

  • some another (binary encoding and store to BLOB as byte stream, XML, etc).

Each time when you need to set the relation you'll need to parse this solid value to separate ID values. The code will be expensive and ugly.

RDBMS will not check your data consistency. You may emulate it using triggers/constraints, but (see previous sentence).

0

needed_for_bundle is your foreign key in products table which is gonna be related with Bundle table through id field. Besides needed_for_bundle must be same datatype than id field in Bundle table. In Products table you defined needed_for_bundle as INT(2) and implicitly as signed while than Bundle table, you defined id as INT(6) unsigned.

Storing 4 products, you should develop a trigger or a stored procedure which is gonna be useful to control the item numbers to be stored.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.