CREATE TABLE vendors (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(100) NOT NULL DEFAULT '',
addr VARCHAR(100) NOT NULL DEFAULT '',
city VARCHAR(50) NOT NULL DEFAULT '',
stateprovince VARCHAR(100) NOT NULL DEFAULT '',
postalcode VARCHAR(20) NOT NULL DEFAULT '',
phone VARCHAR(40) NOT NULL DEFAULT '',
url LONGTEXT NOT NULL DEFAULT '',
PRIMARY KEY(id)
)
CREATE UNIQUE INDEX v_n ON vendors(name);
this has a built-in relationship (for lack of a better term).
CREATE TABLE foods (
id INT AUTO_INCREMENT NOT NULL,
name TEXT NOT NULL DEFAULT '',
botanicalname TEXT NOT NULL DEFAULT '',
vendor_id INT NOT NULL DEFAULT '0',
servingsize TEXT NOT NULL DEFAULT '',
PRIMARY KEY(id)
)
CREATE TABLE foodpopularnames (
id INT AUTO_INCREMENT NOT NULL,
name TEXT NOT NULL DEFAULT '' COMMENT '',
PRIMARY KEY(id)
)
this is a relationship table.
CREATE TABLE foodpopularnames (
id INT AUTO_INCREMENT NOT NULL,
foodpopularnames_id INT NOT NULL DEFAULT '0',
foods_id INT NOT NULL DEFAULT '0',
PRIMARY KEY(id)
)
CREATE UNIQUE INDEX f_n ON foods(name)
CREATE INDEX fpn_fi ON foodpopularnames( foodpopularnames_id )
the indexes speed access up.
creating the php app is going to be a fairly difficult,since you have 2 relationship tables, and you haven't done any php database programming before. I can't fit it all in here on yahoo answers. you still need to make your SELECT, INSERT, UPDATE, and DELETE queries, make an admin section for maintaining the foods, have mulltiple kinds of display pages, etc. it's not a simple undertaking. well, maybe if you are doing dreamweaver and you have the appropriate book handy like dreamweaver bible. they have a section on dynamic data.
TEXT is bigger than VARCHAR, LONGTEXT is even bigger.
you will need to