[El Forum]

[eluser]Ciaro[/eluser]
Hi there.

I was wondering, how do you guys name your tables and columns (fields) in MySQL/MSSQL?

I've been doing some googling, but there are just too many different approaches. I'm yet to figure out a consequent way that suits me best.

Should I use id or tablename_id as primary key? What about foreign keys? Singular or plural table names? Short or long table names? tablename_fieldx or just fieldx?

I currently already have made up my mind about following things:

'lowercase' all the way
underscores above CamelCase

--

Thanks.

[El Forum]

[eluser]sophistry[/eluser]
There is good reason to adopt a convention. It will help you think about the data model, but also, it will make it easier to automate certain tasks when interacting with the db.

Check out IgnitedRecord. Look at the manual and find the section that talks about the conventions: id, plural tablenames, singular model names, foreignkeytablename_id, and more...

In addition to being well-documented, it looks like a nicely written bit of code.

[El Forum]

[eluser]Colin Williams[/eluser]
I've always been one to use singular table names. I also avoid camelCase like the plague. Best thing to do is keep things clear and concise. Whatever methods or conventions you adopt, just stick to them and remain consistent.

[El Forum]

[eluser]Eric Cope[/eluser]
I stick to what makes sense. I prefer camelCase because it is easier to read for me.
I also keep table and column names appropriately (read obviously) named, avoiding acronyms. Trying to figure out what nmOfBsnss means six months after the project is over is a bad plan. Even if it means replacing it with nameOfBusiness or name_of_business, those extra key strokes now will pay off later.

[El Forum]

[eluser]PV-Patrick[/eluser]
I always use appropriately named, well defined tables with underscores, not camelCase. I find it's easier to read and remember when it's 6 months+ down the line...

As far as column names and primary keys; I use the same method as the tables, whatever is most appropriate. If it's something that is more of a record, I use record_id. If it's something that is in need of a more descriptive purpose like a table of customers, I use customer_id. What I have found over the years is to be as descriptive as possible. It really helps in the future whether it's you trying to go over your code or someone else in a team/work environment.