Advise on table (s) structure

I re-doing a website for a restaurant. The old datatabase is kind of a mess. For example there is a table menu_items, holding the different menus. The structure is like this

CREATE TABLE IF NOT EXISTS `menu_items` (
  `menu_item_id` smallint(2) NOT NULL AUTO_INCREMENT,
  `menu_group_id` smallint(2) NOT NULL,
  `menu_item` varchar(255) DEFAULT NULL,
  `description_dut` text,
  `description_eng` text,
  `description_dut2` text,
  `description_eng2` text,
  `description_dut3` text,
  `description_eng3` text,
  `description_dut4` text,
  `description_eng4` text,
  `description` text,
  `price` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`menu_item_id`),
  KEY `menu_group_id` (`menu_group_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

where the diffrent description_(s) represent the different receipes in the menu. The problem is though that some menu_items only use the first 2 two descriptions (description_dut, description_eng) other menu_items use the first 4 (description_dut, description_eng, description_dut2, description_eng2) and so on. I am sure that this is not really very good practice. What would be a better sollution? Having the different descriptions in a separate table? Any suggestion would be more that helpful.

Thank you in advance

oh, yes

Hi rudi. And what would be the best structure for thi table in your opinion?

having the descriptions in a separate table :slight_smile:

What fields do you think I should use?

CREATE TABLE recipes ( id INTEGER NOT NULL PRIMARY KEY , description_dut VARCHAR(127) , description_eng VARCHAR(127) );

Hi Rudi. and a foreign key menu_item_id I presume?

okay, well, just stop and think about it for a second

that would mean a particular recipe could belong to only a single menu

is that what you want?

Hi Rudi. Yes indeed, that is what I mean it is not a very big card and each menu is unique

so a menu can have multiple recipes, but each recipe can belong to only one menu?

Yes that is indeed the case

When you first are remodeling the database, I would recommend taking the separation one step further.

CREATE TABLE recipes
( item_id INT NOT NULL,
, language_iso CHAR(2) NOT NULL
, description TEXT
PRIMARY KEY(item_id, language_iso)
);

This will make it much easier adding another language to the menu in the future. If this step is something that is needed, is another question, but doing it is a good practice for a future project where you will need this. Time wise handling it this way, should not take more time programming.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.