Database naming conventions

[eluser]Dam1an[/eluser]
@kimon: I would have thought that at that scale (1500+ tables) you'd want to avoid TLAs in favour of something a little longer, but much clearer
If you need to keep referencing some docs to see what CUS means, then its not very efficient

[eluser]jedd[/eluser]
gary - this is what happens when there are multiple garys .. err, garies .. err, you know what I mean. And of course I use the word alas in regular conversation. I'm wary of people that eschew gratuitous anachronisms.

kimon - fascinating insight. I've seen a schema (graphic) dump, in very small fonts, that have been printed out onto two x A0 pages .. and my nose started to bleed. Mind, I'd already seen how ugly the application it powered was, so I was probably prejudiced.

Dam1an - I used to be a big proponent of natural keys, but a guy I knew back in Sydney, who was way smarter than me, explained why they were evil ... alas (!) my brain isn't sufficient to recall the details, only the resultant belief that they are, in fact, evil. I think compound keys are a nice idea. I like that you get that guaranteed uniqueness of relationships, as you observe, as it fits my preference for keeping data-rules as close to the data as possible (a la restraints on FK's, f.e.) But OTOH I like the consistency and reliability of the id field being present in every table (despite not really using AR) and I'm happy to guarantee uniqueness of any such relationships in the code proper.

Actually I could probably better sum up my position thus:

I consider compound keys a sub-set of natural keys, and I eschew them first and foremost.


MySQL's future .. is indeed an interesting thing to ponder. I suspect we'll see a fork at some point soon, and/or a bunch of key developers shift to the postgres camp. There's compelling business reasons to keep MySQL going, within Oracle Corp, but nonetheless it'll probably get ugly. Watching what happens to NetBeans will be instructive, I suspect - rumours suggest it'll be an early casualty. Larry's generally regarded as far more ruthless / greedy / less sentimental / less experimental (take your pick) than Jonathon, Scott, et al ever were.

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242432659"]MySQL's future .. is indeed an interesting thing to ponder. I suspect we'll see a fork at some point soon, and/or a bunch of key developers shift to the postgres camp. There's compelling business reasons to keep MySQL going, within Oracle Corp, but nonetheless it'll probably get ugly. Watching what happens to NetBeans will be instructive, I suspect - rumours suggest it'll be an early casualty. Larry's generally regarded as far more ruthless / greedy / less sentimental / less experimental (take your pick) than Jonathon, Scott, et al ever were.[/quote]

I think I remember reading a few days ago that some of the key dev(s) are starting a new fork of MySQL... wether this will continue with it as was planned before the aquisition though, we'll have to wait and see

[eluser]garycocs[/eluser]
[quote author="jedd" date="1242416213"]gary - sorry, I meant to come back to this one earlier.

I'd strongly recommend you have a read about [url="http://en.wikipedia.org/wiki/Foreign_key"]FK's at wikipedia[/url]

As I mentioned, I think MySQL's implementation of constraints for FK's is a little, err, incomplete. I've never really delved into it, but a quick read of the ref man (5.1) suggests InnoDB has half-way decent support, but other DB types don't get the benefits (cascading deletes, limit/checks on create, etc).[/quote]

Nice one for that Jedd, I'll have to do a bit more research, nuts that I didn't really know anything about DB construction but ended up with a type of Foreign Key anyways?? Reckon I'll stick with MySQL anyways as it's what I know and just hack away at it to see what I can get??

[eluser]HDuqueC[/eluser]
Hi,

It is interesting to tell that there isn't and there will not be a standard for database naming conventions. I think that does not exist a "perfect" one because those standards that could be ok for one project, could not be that good for a different one.

Anyway, it will be a endless discution if someone tries to persuade the others which approach is better than the other.

I like this one:

http://www.interaktonline.com/Support/Ar...id_asc=221

Regards,

Nando.

[eluser]Unknown[/eluser]
My way, inherited this convention at faculty:

dbname: mydb
tbl name: fubar
tbl field: id
tbl fk : id_tableB [easy to spot...]

all FK are at the top of table:
id
id_tblB
id_tblC
name
description

[eluser]pashj[/eluser]
I'm no expert, by any means. But I prefer prepending the table name to the columns as the original poster suggested. The reason being duplicate column names in different tables. EX.

Code:

PRODUCT TABLE
product.id
product.title
product.description
etc...

CATEGORY TABLE
category.id
category.title
category.description
etc...

QUERY
select * from product p join category c on p.id=c.id;

The result for 'title' and 'description' come from the product table. You could reverse the join, then the result for duplicate columns would come from the category table. (To be fair, both columns are retrieved, but only one can be accessed via the name. You can get both by using an index.)

OK, so what do I do if I wanted both titles and descriptions? Yes, aliases would do the trick:

Code:

select
p.title as `product_title`,
p.description as `product_description`,
c.title as `category_title`,
c.description as `category_description`
from
product p join category c on p.id=c.id;

But this means every query needs to be carefully constructed (time, effort, possibility for bugs). It seems like the long way around. If the column names were already prepended with the table name

Code:

product.product_id
product.product_title
product.product_description
etc...

Then the result set would be unique with no extra work. I can continue to use select * queries. I can add/remove columns without having to dig through the code and make changes.

It just works. It's verbose, but it works. IMHO

[eluser]jedd[/eluser]
Hi pashj and welcome to the CI forums.

You've certainly chosen a gnarly subject to come in on

When you say:
[quote author="pashj" date="1259522235"]
But this means every query needs to be carefully constructed (time, effort, possibility for bugs). It seems like the long way around. If the column names were already prepended with the table name

Code:

product.product_id

[/quote]

My standard response is 'I type 80 words a minute, so typing this:

Code:

product.id AS product_id

instead of your code doesn't seem a big enough imposition to me.'

Plus of course if you just want id and title from the product table, here's our comparative queries:

Code:

// jedd's
SELECT id, title FROM product;

// pashj's
SELECT product_id, product_title FROM product;

So ... claims of less code (with less errors) are a hard one to substantiate! But we've been through this before - it's all swings and roundabouts, I know.

Anyhoo, a more interesting question is what format do you use to denote your foreign keys?

[eluser]Colin Williams[/eluser]
I pretty much follow everyone else's formula. I'm a singular table name guy. It's the t table, not a table of ts.

And I try to keep id fields unique in someway, like mid, pid, nid, cid, etc., at least for the main tables. Otherwise it's just 'id' and FKs can be named [foreign_table]_id

[eluser]thomas.ernest[/eluser]
Hi there,

Just to let you know that I'm used to develop with PHP_CodeSniffer [phpcs] to check the coding convention of my php code.

However, it doesn't support Code Igniter style guide [ci-style] natively. So, I've developed something.
There is no support for SQL syntax in PHP strings yet, but there is for all the PHP code around

[phpcs] http://pear.php.net/package/PHP_CodeSniffer/
[ci-style] http://ellislab.com/codeigniter/user-gui...guide.html
[ci4php-cs] https://github.com/thomas-ernest/CodeIgn...odeSniffer