CODEWITHSUNDEEP
mysqldatabase
zozo
zozo

Reputation: 8582

mysql - add columns to a table or create new table and join

I have the following setup:

A table with n columns that contain let's say... pizza details. There are 4 kind of pizzas at this point Until now all pizzas had same details, each detail saved in a column.

But now a new kind of pizza called super pizza appeared, that has 2 more types of details that needs to be saved.

There are 2 ways to do this (that I can think about):

  1. Add to columns to the existing table, and leave them blank/null/whatever for the rest of 4 pizzas types

  2. Create a new table with 3 columns (idPizza, newDetail1 and newDetail2), save pizza as the rest of pizzas, and when I get data join the 2 tables

First option has the disadvantage that I keep useless data for all pizzas except the new type (80% of the table at an average pizzas distribution).

Second option has the disadvantage that each time I get the new kind of pizza I need to make a join. Also the db will be kind of "messy"... having part of pizza element stored in a table and other part in another.

I know that ultimately is a matter of taste but how do you think I should proceed? I incline a bit to first option but a second opinion is welcomed.

As a note: There are lots of entries in the tables (like tens of thousands).

Second note (based on an answer): Can't refactor the "pizza" table, just add to it.

Upvotes: 3

Views: 541

Answers (3)

Ajeet Singh
Ajeet Singh

Reputation: 32

Well I suggest just create a table with attributes (idPizza, newDetail) and save data as below

idPizza, newDetail1 1 1 detail 2 2 detail 3 super detail 3 super detail second

MAke idPizza, newDetail1 as primary

Upvotes: 0

Ganesh Bora
Ganesh Bora

Reputation: 1153

instead of this you can use flat table concept as follows whic hwill be easy for adding no of characteristic in future for new pizzas having some more details

 CREATE TABLE pizza 
    (
     id int auto_increment primary key, 
     name varchar(20), 
     details varchar(30)
    );

INSERT INTO pizza
(name, details)
VALUES
('pizza1', 'nice pizaa'),
('pizza2', 'nice pizza@sqlfiddle');


CREATE TABLE details 
    (
     id int auto_increment primary key, 
     name varchar(20)

    );

INSERT INTO details
(name)
VALUES
('pizza_size'),
('price');

CREATE TABLE pizza_details 
    (
      id int auto_increment primary key, 
     pizza_id int(10) , 
     details_id int(10) , 
     details_value varchar(20)

    );

INSERT INTO pizza_details
(pizza_id,details_id,details_value)
VALUES
(1,1,'small'),
(1,2,'Rs 20'),
(1,1,'big'),
(1,2,'Rs 50');

and fire the query

select pizza.name as pizza_name,details.name as detail,pizza_details.details_value  from pizza_details left join details ON pizza_details.details_id = details.id
left join pizza On pizza_details.pizza_id = pizza.id;

SQL Fiddle

Upvotes: 1

JDGuide
JDGuide

Reputation: 6525

I suggested for use first option. Because 2nd option reserve more resources than 1st option and it will take more time process.

As the disadvantages of 1st option you have mentioned is quite obvious,but when you need more column then add new to that existing table.

Upvotes: 0

Related Questions