MySQL Forums
Forum List  »  Newbie

Re: How to allow for 'unlimited' fields in an attribute.
Posted by: Peter Brawley
Date: September 06, 2014 03:46PM

When you find yourself writing column names ending in ...1, ...2 &c, stop and think: no matter how many ...N column names you declare, the real world can always ask for one more. The solution is a child table, eg ...

create table pizza( 
  pID int unsigned primary key auto_increment, 
  price decimal(5,2), 
  kj int, 
  name varchar(32)
);
create table pizza_ingreds( 
  inID int unsigned primary key auto_increment, 
  ingred varchar(32),
  pID int unsigned,
  foreign key(pID) references pizza(pID)
);

Now a pizza can have as many ingredients as anybody can write down.

And notice, no 'slice' column in the pizza table. You appear to need a third table for your products eg ...

pizzaproducts(
  prID int unsigned primary key auto_increment, 
  prname varchar(32), 
  pID int unsigned, 
  foreign key pID references pizza(pID)
)

to hold entries like 'slice', 'whole pizza', chalf pizza' &c, or some variation on that.

Read about normalisation eg at ...
http://www.artfulsoftware.com/dbresources.html
http://www.artfulsoftware.com/dbdesignbasics.html

Options: ReplyQuote


Subject
Written By
Posted
Re: How to allow for 'unlimited' fields in an attribute.
September 06, 2014 03:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.