Welcome Guest, Not a member yet? Register   Sign In
Database naming conventions
#31

[eluser]Michael Wales[/eluser]
If you set NOT NULL and don't define a default I believe MySQL just assumes '' - then I simply don't pass that data to the database at all.
#32

[eluser]jedd[/eluser]
I'm completely averse, these days, to using natural or compound keys - so even a table that was just two columns of ID's from two other tables, I'd still have a SERIAL (INT, AUTOINC, NOT NULL etc) id column in there.

Michael, your comments above suggest that you always have a 1:1 relationship between tables and models - is that strictly adhered to, or just a rule of thumb?
#33

[eluser]Dam1an[/eluser]
[quote author="Michael Wales" date="1242408154"]If you set NOT NULL and don't define a default I believe MySQL just assumes '' - then I simply don't pass that data to the database at all.[/quote]

Really, cause when I tried that, I kept getting column_name cannon be null :-S
#34

[eluser]jedd[/eluser]
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).
#35

[eluser]slowgary[/eluser]
[quote author="jedd" date="1242404778"]Alas, the language has lots of idiosyncrasies, and it's easy to identify any number that substantiate your particular POV on the day. I was thinking about this earlier today, as I got some socks from my sock drawer, and then grabbing a few coins from our coin bowl on the way out.[/quote]

Code:
sock drawer == drawer of socks && coin bowl == bowl of coins

After reading this thread I do understand now the use of singular table names. I agree it comes down to how you view a database. Seeing things like `user.salary` does indeed make sense to me.

I hope though, for your sake Jedd, that you don't use the word 'Alas' in speech but only in text. I don't think your über intelligence will be taken as such. Rather, you will appear to be a time traveler from the 18th century. But then, maybe you ARE a time traveler from the 18th century. ;-P
#36

[eluser]slowgary[/eluser]
Yes, foreign keys on InnoDB only, I'm aware. Did we have that conversation?

Sorry to post off topic, but it really sucks that this is the case. I think InnoDB tables suffer a higher rate of corruption than MyIsam. It also sucks that if you want the benefit of foreign keys you no longer get to choose between table level locking / row level locking. I did read somewhere that MySQL was working on a new engine called Maria, and that it was the engine to end all engine wars. Does anyone know anything about that and when it will be released for production?
#37

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242410034"]I'm completely averse, these days, to using natural or compound keys - so even a table that was just two columns of ID's from two other tables, I'd still have a SERIAL (INT, AUTOINC, NOT NULL etc) id column in there.[/quote]

Out of curiosity, why are you adverse to compound keys?
I used to always have an auto id in relationship tables, but realised I never used the id field
Another potential problem with using an auto ID in this case is you can get the same relationship twice

EDIT: @ slowgary, Was that announcement before the Oracle aquisition, I wonder if it will still happen if it was
#38

[eluser]slowgary[/eluser]
Oracle acquisition? Shows how much I know. I found the answer to my own question (which I should've done in the first place). Maria and also the Falcon engine will both be a part of MySQL 6.0, which is currently alpha.

Let's hope Larry Ellison has enough sense not to destroy MySQL's openness and freeness.
#39

[eluser]tekhneek[/eluser]
A little late but who cares, this is how I handle my "database" (I'd usually call them databases, but pluralization is apparently the devils.)

Table naming conventions
- Plural (e.g. "users")
- 1:* relationships are: "table1_table2" (e.g. "users_categories" maintaining pluralization throughout.)

I don't use the alphabetical approach because "categories_users" sounds confusing to me where as "users_categories" sounds logical and you can immediately understand the significance (at least I can) of that naming convention.

You could say "users have categories/categories have users"... I mean does the dog wag the tail or does the tail wag the dog?

- *:* relationships are spread over 3 tables (almost always)
Table 1: photos
Table 2: photo_galleries
Table 3: photo_galleries_photos

Column conventions
- Always lowercase
- alpha-underscore
- Primary keys are always first -- "id" (e.g. users.id)
- Foreign keys are always fk_table_id (e.g. fk_user_id) following any primary id. (Pluralization is stripped to signify a single relationship.)
- Last column is always "created" datetime/timestamp regardless of table significance albeit joining, primary, secondary, tertiary, etc.

photo_galleries_photos example
fk_photo_galleries_id
fk_photo_id
created

That's my two cents.
#40

[eluser]kimon[/eluser]
Hi All,

after some (many) years in designing database for large systems (> 1,500 tables) in various database engines, I have concluded and used the following:


- Each table represents an entity so it is in singular (e.g Customer). It's database table name will be CUS. That is the TLA (Three Letter Abbreviation) of the entity name.

- Each field of Customer Entity, thus CUS table, will start with TLA of the entity. CUSID, CUSCODE, CUSNAME, etc.

- Foreign Key fields in that entity / table, are the exact field names of the primary key that references, e.g. PRFID which is the foreign key field to the PROFESSION entity / table that has the TLA, PRF.

- table constraints (Primary Keys, Foreign Keys, Unique Keys, etc) will be prefixed with the entity / table name, underscore, then the constraint type and as follows (in the example context of Customer) and then (where applies) the field(s) used: CUS_PK, CUS_FK_PRFID, CUS_UNQ_CUSCODE


- possible views, stored procedures or other objects (depending on the database engine: like generators in firebird/interbase, sequences and packages in oracle, etc) will be prefixed or will include the entity TLA that belongs


Of course in large scaled projects with many entities / table and many people on the project team, some tools are necessary to help with this rules. Apart from the design documents, a TLA database is a must have as part of a good documentation on this rules. :-)



That's my part of the story of the database naming conventions I use.




Theme © iAndrew 2016 - Forum software by © MyBB