On Fri, Apr 8, 2011 at 9:23 PM, Logan Best <loganbes...@gmail.com> wrote:
> Hi,
>
> I am new to CakePHP and wasn't really understanding the database
> structure naming conventions entirely, or maybe I am, I don't know.
> I was mainly wondering if someone could rewrite this DB Structure to
> the appropriate naming convention:
Here you go:
CREATE TABLE `ambiances` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO ambiances (name) VALUES
('Casual Fine Dining'),
('Waterfront'),
('Rooftop'),
('Patio'),
('Beach'),
('Romantic'),
('Casual'),
('Eclectic'),
('By Boat'),
('For Kids'),
('Dog Friendly'),
('Fine Dining'),
('Outdoor Dining'),
('Water Front Dining');
CREATE TABLE `cuisines` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO cuisines (name) VALUES
('American'),
('Bakeries'),
('Banquet'),
('Bar Tavern'),
('Barbecue'),
('Breakfast'),
('Brunch'),
('Buffet'),
('Cajun'),
('Caribbean'),
('Catering'),
('Chinese'),
('Coffeeshops'),
('Deli'),
('Delivery'),
('Dessert'),
('English'),
('Fondue'),
('French'),
('Greek'),
('Hawaiian'),
('Indian'),
('Italian'),
('Japanese'),
('Live Entertainment'),
('Mediterranean'),
('Mexican'),
('Nightlife'),
('Organic'),
('Pizzeria'),
('Seafood'),
('Southern'),
('Southwest'),
('Sushi'),
('Tapas'),
('Thai'),
('Vegetarian'),
('Vietnamese');
CREATE TABLE `locations` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO locations (`name`) VALUES
('Bluffton'),
('Broughton'),
('Brunswick'),
('City Market'),
('Claxton'),
('Downtown'),
('Garden City'),
('Gateway'),
('Hilton Head'),
('Historic District'),
('Midtown'),
('Oglethorpe Mall'),
('Pooler'),
('Richmond Hill'),
('River Street'),
('Sandfly'),
('Savannah Airport'),
('Savannah Mall'),
('Southside'),
('Statesboro'),
('Sylvania'),
('Thunderbolt'),
('Tybee Island'),
('Victorian District'),
('Whitemarsh Island'),
('Wilmington Island');
CREATE TABLE `restaurants` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`location_id` INT(11) NOT NULL,
`name` VARCHAR(150) NOT NULL,
`phone` VARCHAR(20) NOT NULL,
`address` VARCHAR(150) NOT NULL,
`city` VARCHAR(25) NOT NULL,
`state` VARCHAR(10) NOT NULL,
`zip` INT(10) NOT NULL,
`vip_special` VARCHAR(250) NOT NULL,
`url` VARCHAR(50) NOT NULL,
`preferred` INT(20) DEFAULT '0',
`image` VARCHAR(255),
`couples_special` VARCHAR(255),
`group_special` VARCHAR(255),
`girlscout_special` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=198;
Cake doesn't use ENUMs. Lucky for you, the columns can all be changed
to boolean (or Cake's version--booleans are handled differently
between RDBMs).
Wait--scratch that. Every one of these should be a row in its table,
not a column. Even a non-Cake DB should be normalised that way.
The table names must be all lowercase and plural. I think the columns
need to be lowercase, also. If not, you'd at least have to put public
$primaryKey = 'ID'; at the top of your models as Cake expects 'id'.
I'd recommend just making them lowercase.
For the associations, you're looking at hasAndBelongsToMany (HABTM)
ambience and cuisine, because several restaurants may share these.
CREATE TABLE ambiances_restaurants (
ambiance_id INT(11) NOT NULL,
restaurant_id INT(11) NOT NULL,
FOREIGN KEY (ambiance_id) REFERENCES ambiances (id) ON DELETE CASCADE,
FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE
CASCADE
) ENGINE=MyISAM;
CREATE TABLE cuisines_restaurants (
cuisine_id INT(11) NOT NULL,
restaurant_id INT(11) NOT NULL,
FOREIGN KEY (cuisine_id) REFERENCES cuisines (id) ON DELETE CASCADE,
FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE
CASCADE
) ENGINE=MyISAM;
You won't get real FKs with a MyISAM engine, but that'll at least
create an index on them for you.
Location should be hasOne because you've got the address info right
there in the table. So each record can only have one location. So I've
included a location_id column.
I'm not sure if I want the couples, group, or girl scout special.
Decisions, decisions ...
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php
