[nycphp-talk] Database, table, and column naming schemes
Matt Juszczak
matt at atopia.net
Sat Sep 12 21:09:35 EDT 2009
> 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
More information about the talk
mailing list