Storing multiple values which are repeated in a table field

Question

I have three objetcs per se, Clients, Products and Orders. Clients is set up with its own values as are the products. The problem arises when I need to set up a table for the orders since though it only has one client, therefore a one-way relationship is done easily, I cant think of how to make the list of products within the order (which is of a variable size).

Eg case:

Client table has following fields:ID,Name

Product table has following fields: ID,Name,Price

Now in order to create a table for orders I have this problem:

Order:

 Id = 001

 Client_ID = 002

(linked to client table)

Products = array? eg. ["milk","tomatoes","Thin_Crust Ham & Cheese Pizza no_Gluten"] (would use their ID this is just to visualize it)

When I first searched for this the most common answer was to create another table. From what I have seen creating another table is not really possible since in those examples they are unique within the newly created table (eg. someone wanted to create a field to store multiple phone numbers for one person within the "person" table, so they can create a table of telf.numbers since they are unique and links them back to the "person" in question.)

The other option I have seen is just using a large varchar field with commas in between values. If this is the only other way of doing so would there not be a problem if we reach the char limit per field?

I've created an inventory system that works similar to this. The solution that I've found for what is my "PartList", which contains a number of part ID's, is to put them in JSON format. In your example it would look something like this: [{"ProductID:" "213"}, {"ProductID:" "211"}, {"ProductID:" "113"}]. I'm not sure if this is quite what you're asking, but this is how I accomplished something similar in my application. — Michael, Jan 03 '20 at 18:36
You need in more than 3 tables. Optimal - 4 tables (i think so). Separate `Clients`, `Products`, `Orders` (referred to Client), `OrderLines` (referred to `Orders` and `Products`, many lines per one order). Any "array-like" datatype, including serialized (JSON, XML,etc.) is not safe solution. — Akina, Jan 03 '20 at 19:38
See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574 for more disadvantages of using the comma-separated list design. — Bill Karwin, Jan 03 '20 at 20:11

Chris Schaller · Accepted Answer · 2020-01-03T20:49:25.597

This is a very common scenario in database design, you are looking to create a n:m (Many to Many) relationship between the order and the product. This can be achieved with a linking table.

you could use a comma-delimited string, JSON, XML or other serialization method to store this data in a single string column, but that complicates the querying of your data and you lose some of the power that using an RDBMS gives you.
Other RDBMS allow VARCHAR(MAX) which alleviates the field length issue when storing serialized data like this, in MySQL just set the field length to a very large number, or use the max value like VARCHAR(65535). See this topic for more help if you go down this route.

In the conceptual case of an Order, this is generally solved by adding a child table OrderItem. (or OrderLine) If you see this data in a report of a receipt, each of these items is a line on the receipt so you might see this referred to as a Line or Line Items approach. The minimum fields you need for this in your model are:

ID
Order_ID
Product_ID

Other common fields you might consider for a table like this include:

Qty: for scenarios where the user might select Extra Tomatoes, or you can simply allow multiple rows with the same Product_ID, perhaps you want both?
Cost_TaxEx: total cost of the Line Item excluding tax
Cost: total cost _including_tax.

This can be minimally represented in SQL like this:

CREATE TABLE Client (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100)
)

CREATE TABLE Product (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    Price DECIMAL(13,2) /* Just an assumption on length */
)  

CREATE TABLE Order (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Client_ID INT NOT NULL,
    /* ... Insert other fields here ... */
    FOREIGN KEY (Client_ID)
        REFERENCES Client (ID)
)  

CREATE TABLE OrderItem (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Order_ID INT NOT NULL,
    Product_ID INT NOT NULL,
    /* ... Insert other fields here ... */
    FOREIGN KEY (Order_ID)
        REFERENCES Order (ID)
        ON UPDATE RESTRICT ON DELETE CASCADE, /* the cascade on order:orderitem is up to you */
    FOREIGN KEY (Product_ID)
        REFERENCES Product (ID) /*DO NOT cascade this relationship! */
)

The above solution allows any number of Product entries in an Order but will also allow duplicate Product's, If you need to enforce only one of each product per Order, you can add a Unique Constraint to the OrderItem table:

CREATE TABLE OrderItem (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Order_ID INT NOT NULL,
    Product_ID INT NOT NULL,
    /* ... Insert other fields here ... */
    UNIQUE(Order_ID,Product_ID),
    FOREIGN KEY (Order_ID)
        REFERENCES Order (ID)
        ON UPDATE RESTRICT ON DELETE CASCADE, /* the cascade on order:orderitem is up to you */
    FOREIGN KEY (Product_ID)
        REFERENCES Product (ID) /*DO NOT cascade this relationship! */
)

Correct, but this is an **m:n relation** between orders and products; an order can have many products and a product can appear in many orders. — Thorsten Kettner, Jan 03 '20 at 19:54
Is that a problem? (do you want __1:n__ instead of __n:n__) if so add a constraint to the table or manage it in the UI. OP's wording indicates that forcing uniqueness might cause problems in their design — Chris Schaller, Jan 03 '20 at 19:57
This solution enables another important feature: keep a record of the price paid for each product at the time of the order. This allows the product price to go up or down, but you can keep an accurate record of revenue. — Bill Karwin, Jan 03 '20 at 20:11
And allows for logic in your UI like 'specials', 'discounts' on individual items or sales rep overrides. perhaps one item is damaged, or is smaller than usual. What if `Price` is actually `PricePerKg` then the `OrderItem` has a `WeightKg` field and the `Cost` is now a calculation of the `WeightKg` x `PricePerKg`... It's a simple model to extrapolate into all sorts of applications. — Chris Schaller, Jan 03 '20 at 20:15
Sorry, with my comment I just wanted to say that while your solution is correct and good, the explanation isn't correct, because this is an m:n relation, not a 1:n relation as you say in the first paragraph. A 1:n relation would be an order that can only contain one product for instance. — Thorsten Kettner, Jan 03 '20 at 20:32
I was trying to say it could be either and have updated the wording to reflect this. Also with each usage you can see I did already have specific clarification. IMO m:n as referenced here (https://stackoverflow.com/questions/3397349/meaning-of-nm-and-1n-in-database-design) is probably out of scope for OP if they are asking this type of question, so I preferred to use `1 or n` notation as it is simpler to visualize. — Chris Schaller, Jan 03 '20 at 20:42

Storing multiple values which are repeated in a table field

1 Answers1