NYCPHP Meetup

[Matt Juszczak]

> I used to use the former method almost exclusively.  However, as I started 
> playing with various frameworks, I've switched to the latter as those I've 
> worked with kind of expect it.  Probably because various _call() based magic 
> ends up looking nicer in userland code.
>
> If you have more specific considerations, feel free to get more specific.

Hi Tim,

Actually, you probably just covered about 99% of what I've read today, 
which is both a good and a bad thing - first, it's good because I know 
that information is out dated, but bad because I still have a few 
questions!  But I'll try to clear my questions up.

I've always used the second method you recommend.  It works well for a 
schema like this:

CREATE TABLE `country` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `state` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `county_id` int(11) unsigned NOT NULL,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `city` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `state_id` int(11) unsigned NOT NULL,
   `name` varchar(100) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

each city has a state_id, and each state, has a country_id.  And since 
cities, states, and countries all are sort of self explanatory, the schema 
sort of documents itself.  But what if you're developing a billing system? 
That's where it's hard, because things are different:

For instance:

CREATE TABLE `account` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `type_id` int(11) unsigned NOT NULL,
   `companyName` varchar(25) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `account_type` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `type` varchar(255) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `customer` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `account_id` int(11) unsigned NOT NULL,
   `firstname` varchar(25) NOT NULL,
   `lastname` varchar(25) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

As you can see, each customer as an account_id.  But each account also has 
type_id.  I chose to do this like I did above, but why didn't I call the 
tables:

customer
customer_account
customer_account_type

After all, that would have worked the same way, right?  So I never really 
know where to put the top level.  Is it just up to the person?  My guess 
is, does it just only matter if the table names aren't self explanatory? 
For instance, if I just had a table called "type", that isn't really 
verbose, which is why I put account_ in front of it.

Also, as you can see, I only tend to use underscores when I'm referencing 
a column in another table.  type_id for instance is account_type.id. 
Which is why I did "firstname" or "companyName" (I did this on purpose to 
show two possibilities).  Which one is the better one to use? 
first_name/last_name/company_name or firstName, lastName, companyName? 
The problem is, if I use first_name, but I'm also using type_id, then some 
people might think that first_name references a column in the first (or a 
similarly named) table called "name".

Thanks for your help!

-Matt