CODEWITHSUNDEEP
sql
Rick Ng
Rick Ng

Reputation: 69

Multiple values in the same column in database

I am doing an order food online. But I am wondering if I can have 2 values in a same column. Or is there any way I can do something like that?

The DB structure:

id | product_code | product_name | price

Sample data:

1 | 'A01' | 'Chicken Fired Rice' | 6.50 10.50

So let say the chicken fried rice has two sizes regular for $6.50 and large for $10.50. Is there any idea how I can create a DB like this?

Thank you.

Upvotes: 0

Views: 2332

Answers (4)

Desorder
Desorder

Reputation: 1539

That is bad design from a DB perspective. To help you avoiding problems like this, there are a set of rules call Normal Forms.

There are many ways to work around this problem. One is to add another column to your table "size" for example and insert to rows one for a small and another for large.

In case you really want to use that design, I'm sure you could same a String with comma separated values and pipes and use to creativity to break this String to size prices. :)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Don't store information like that in price column it violates the First Normal Form, It will be difficult to parse the data

I will go with two tables

One to store the product information and another table to store the price.

Product table

Create table Product
(
Product_Id int, --Auto generated 
product_code varchar(10),
product_name varchar(100)
)

Price Table

Create table Price
(
Price_Id  Int, --Auto Generated
Product_Id int, --Foreign key column referred from product table
Size varchar(20),
Price Decimal(10,2)
)

or If it is always two size then you can create single table with two column's to store Price of Regular and Large size.

Create table Product
(
Product_Id int, --Auto generated 
product_code varchar(10),
product_name varchar(100),
Regular_size_price Decimal(10,2),
Large_size_price Decimal(10,2)
)

Upvotes: 1

kungphu
kungphu

Reputation: 4849

There are several ways; one is using a JSON field, something that's not universally supported. Though this is not a good idea in most situations, including yours.

Normalization is the process of breaking your database structures into logical relational parts (tables). For example:

Products: id | code | name

Prices: product_id | size | price

This way you can have any number of prices for any given product; product_id is a foreign key, a reference to the id field of a product. There's certainly other information that could/should/will be added to such a table structure, but this is the basis of the sort of flexibility you're asking for.

Upvotes: 0

alexander.polomodov
alexander.polomodov

Reputation: 5524

It's bad idea, your db design violates the first normal form: https://en.wikipedia.org/wiki/First_normal_form.

With this violation it will be much harder to write select against such table.

Upvotes: 1

Related Questions