CODEWITHSUNDEEP
mysqlrow
Arpan Biswas
Arpan Biswas

Reputation: 185

Can a table have multiple records of a single column in a single row?

There is a table 'items' with columns : item_name, i_code, items_left & price. It stores all the items that a shop sells. There is another table 'customers'. It stores record of all the customers who visited the shop. I want to keep record of all the items that a particular customer bought. I want to create a column 'items_bought' in 'customers' table, that will store item codes of all the items a particular customer bought. But having more than one item code for a particular customer row is impossible. Please help me have multiple records of items_bought in a single row of customers.

Upvotes: 0

Views: 656

Answers (2)

mbenegas
mbenegas

Reputation: 481

The answer is, yes you can have multiple values in a single field for each row. A comma separated field could be a solution.

If you are using a relational database, your life will be easier if you create a new table, let's say items_bought, that will hold the relation between customer and item bought.

For example

create table `items_bought`
 (id int not null primary key,
 item_id int not null,
 customer_id int not null)

Each field item_id and customer_id will have a foreign key to items.id and customers.id table fields respectively.

This way you don't need to manage strings and parse comma separated values. You can simply query your tables like:

select * 
from `items` i 
 inner join `items_bought` ib on i.id = ib.item_id
 inner join `customers` c on ib.customer_id = c.id

The above query will return all customer and item information of customers that have bought at least one item.

Upvotes: 1

Vishmay Shah
Vishmay Shah

Reputation: 21

this is possible,but not suggested, you can save your item code in customer table with
or with comma or environment.newline

but it has not any use except display

my suggested solution is create a new table CustomerItem, having CustomerId,ItemId and other common attribute, that should b between Customer& Item like purchase rate, time of purchase etc(you cannot do it in above method)

Upvotes: 1

Related Questions