3

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.

Improve this question
3
  • Is it possible more than 3 column Commented May 10, 2013 at 7:18
  • Of course it is... that's just one of the ideas I had. Commented May 10, 2013 at 7:22
  • you can simply add column to it Commented May 10, 2013 at 7:42

3 Answers 3

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yeah... unfortunately can't really change something that worked and has code written on for the last 6 years :). I'll add in question that I can't delete/refactor first table, just add to it. Still +1.
you can take one time effort to do this as this structure is very flexible for adding n number of details to any item.
Is there name for this pattern?
0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.