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.
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.
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)
)
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. :)
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.
