Are Long Names OK??

[DoubleDee]

Is there a problem having a MySQL table named "article_survey_question"??

Is there a problem having a MySQL field in a junction table named "articleSurveyQuestionID"??


I am thinking I have to join 4 tables to get what I need, and I just can't think of more descriptive names that are shorter, so I figure it is better to err on the side of clarity even if it gives me verbose table and field names?!

What do you think?


Debbie

[markbrown4]

Generally no, you obviously want a short self explanatory name.
A standard for id's is simply just to use 'id' for every table, which is shorter

We'd need to see your schema to see, but I don't know you'd need to join four tables
Does article_questions work as a name?

Another standard I've adopted from Rails is to make all table names plurals e.g. article_questions

[r937]

Is there a problem having a MySQL table named "article_survey_question"??

Is there a problem having a MySQL field in a junction table named "articleSurveyQuestionID"??

no to both

long name, short name, it's up to you

[Mikl]

I agree with the others. I generally prefer names that are fully descriptive, even if it means some extra typing. I also try to avoid non-obvious abbreviations in names as far as possible.

Another important point is consistency. For example, always name your surrogate primate keys, say, ID (as per Mark Brown's suggestion). Going further, name the foreign keys that refer to the IDs as, for example, CustomersID, OrdersID, etc (plural form of the entity name, no underscores). (This is just an example. The point is to adopt a convention, and to use it consistently.)

Mike

[cpradio]

I want to throw in my agreement as well, and this gem that arrived in my RSS feed sometime within the last week so you can at least have a laugh of what you might be dealing with if you opted not to give meaningful names to your columns/tables.

[jasonsmith1976]

No there is no issue with long names

[Mikl]

I want to throw in my agreement as well, and this gem that arrived in my RSS feed.

Thanks for that. It's It's pretty horrible, isn't it.

Mike

[DoubleDee]

Generally no, you obviously want a short self explanatory name.
A standard for id's is simply just to use 'id' for every table, which is shorter

Right. I do that.

But my example was a Foreign Key in my Junction Table...

We'd need to see your schema to see, but I don't know you'd need to join four tables
Does article_questions work as a name?

Far be it for me to stop someone who wants to help some more!!

Check out this new thread about Thoughts-on-Tables-for-Surveys.

Another standard I've adopted from Rails is to make all table names plurals e.g. article_questions

I really dislike plural table names, but I know this is a religious battle...

Thanks for all of the comments!!


Debbie

[Jason__C]

no to both

long name, short name, it's up to you

Spot on. Unless you work for someone else, who gives a crap how it's worded. Just be consistent with how you name the columns, you will have less frustration.

[Antnee]

Personally, I would never use a simple name like "id". It's confusing for one, especially if there are no FK constraints to point you to what that ID refers to. So I make my IDs common across all tables. A quick example would be something like this:

Blog Table
blog_id
blog_title
blog_content
author_id

Tags Table
tag_id
tag_name
tag_count

Blogs2Tags Table
blog_id
tag_id

Authors Table
author_id
author_name
author_email
author_password

It's a very simple example so it may not appear necessary, but I've worked on some systems with 100s of tables and foreign keys are all over. I've seen some where they'd have:

• id
• cid
• tid
• pid

You may work out eventually that they mean:

• id - table PK and row identifier
• cid - category ID. In this case the manufacturer
• tid - tariff ID
• pid - phone ID

Only then you go elsewhere and pid means product ID, and there are still cids, tids and ids. If you're going to use an ID, make it unique. It gets even more confusing when you end up with something like:

SELECT *
  FROM products p
     , phones ph
     , pictures pic
     , people pe
 WHERE p.pid = ph.id
   AND ph.pid = pic.id
   AND pic.pid = pe.id

Wait, what? All of them have an id column and a pid column, and it's not obvious what any of them do! This is better:

SELECT *
  FROM products p
     , phones ph
     , pictures pic
     , people pe
 WHERE p.phone_id = ph.id
   AND ph.pic_id = pic.id
   AND pic.pe_id = pe.id

But this is better still IMHO:

SELECT *
  FROM products p
     , phones ph
     , pictures pic
     , people pe
 WHERE p.phone_id = ph.phone_id
   AND ph.pic_id = pic.pic_id
   AND pic.people_id = pe.people_id

Suddenly the relationships appear much more clear.

Yes, it's a deliberately messy situation created completely off the top of my head, but I've actually been in situations where databases have been that confusing (more so, actually) so I always try to keep field names unique, especially where they'll be used as FKs.

And no, I can't always use FK constraints to make relationships clearer. As we found out when we migrated from InnoDB to NDB Cluster, it didn't support FK constraints (I believe that is now remedied, mind you)

I think it's personal preference, but I'd always try to make it as clear as possible, especially if I'm sure that a) I won't be the only person maintaining it and b) it's likely to grow large

[r937]

Personally, I would never use a simple name like "id". It's confusing for one...

it may be confusing to you, but certainly i agree that the proliferation of different and overly short id names that you just made up are confusing...

... but perhaps i can restate the convention and see if you might not find it simple enough if you grasp the basic idea behind it

the basic idea is this: when the PK of a table is an autoincrement, it's called "id", and when an id isn't the PK, then it's a FK, and it isn't called "id" but rather entity_id, where entity is the table it's referencing

so joins will always look like this --

ON orderitems.product_id = products.id

there is absolutely no ambiguity about the role that each of these columns is playing, and there is no guessing as to which of these is the "one" table and which is the "many" table, since each PK-FK relationship is a one-to-many relationship

dead simple, self-documenting, easy peasy

as to the second, implied, convention in yourt post, antnee, of stuffing the table name into each column name as a prefix, i find this most disagreeable -- you've upped the noise factor without increasing the signal

[Antnee]

Yeah, that works, and that was my second example (mostly)

I still think it's down to personal preference if you're working on your own project, or it's down to a project leader (lead dev? dev manager?) to define conventions that everyone should conform to, and whatever is decided on needs to be actually used. A while back some of us had email addresses like joe.bloggs@company.com while others had jbloggs@company.com and others just joe@company.com, even AFTER the IT manager put a policy in place where all email addresses should be firstname.lastname@company.com, so we had a few different people called Pete (three IIRC) and they all had email addresses following different conventions (no conventions?) and therefore mail used to go to the wrong place.

My point is simply that some kind of convention should be agreed on, and then enforced!

[Mikl]

Antnee,

You've made some good points. I agree with much of what you said.

However, unlike you, I always use ID as the name of the primary key in every table - even for those tables that don't strictly need an additional ID field, such as your Blogs2Tags table. This has saved me a significant amount of time over the years. Whenever I do a join, I never have to think about the linking column name in the parent table: it is always ID. You might think that's not a big deal, but it really does make life simpler, and I'd hate not to be able to do it.

Similarly, I always use a consistent naming system for foreign keys - as you do too. You use the singular form of the entity name, followed by an underscore, followed by the referenced table's primary key. That's fine. I've standardised on the plural form without the underscore. But that doesn't matter. The important thing is that it's the same for every table. Again, the benefit is not having to think about it when writing your queries.

We're drifting away from the original question somewhat, but this is all useful stuff for people to keep in mind.

Mike

[Antnee]

as to the second, implied, convention in yourt post, antnee, of stuffing the table name into each column name as a prefix, i find this most disagreeable -- you've upped the noise factor without increasing the signal

Sorry, missed that bit - assume it was edited in while I was replying

I disagree. Saying product_id = product_id is perfectly clear. I would usually be using shortened aliases in tables though so I would never directly reference products.product_id or phones.product_id, but rather it would be something like p.product_id, ph.product_id. I find it much easier to understand when you're 70 lines down from where you defined the alias.

Again though, I feel that it's personal preference, or you should be conforming to your projects guidelines, whatever they may be. I just disagree with ambiguous column names. The tid, cid, pid etc examples are actually not made up. I worked on that database for a couple of years and I saw every single developer pull his hair out more than once because he'd got mixed up. Mostly because in the products table there was a pid that referred to a phone ID, but elsewhere pid meant product ID and phid was the phone ID.

[r937]

Sorry, missed that bit - assume it was edited in while I was replying

I disagree. Saying product_id = product_id is perfectly clear. I would usually be using shortened aliases in tables though so I would never directly reference products.product_id or phones.product_id, but rather it would be something like p.product_id, ph.product_id. I find it much easier to understand when you're 70 lines down from where you defined the alias.

you're still thinking about the ids, and i think we've all agreed that a PK/FK convention is necessary, and that it needs to make (some sort of) sense, and that it needs to be followed

no, i was talking about the other columns, the ones marked in red here, where the table name is embedded as a prefix in the column names --

Blog Table
blog_id
blog_title
blog_content
author_id

Tags Table
tag_id
tag_name
tag_count

Authors Table
author_id
author_name
author_email
author_password

i find this most disagreeable, in a signal-to-noise kind of way

[Mikl]

i was talking about the other columns, the ones marked in red here, where the table name is embedded as a prefix in the column names -- i find this most disagreeable, in a signal-to-noise kind of way

I missed that too. So, you're referring to the practice of including the table name in the names of the columns within that table (so the Authors table contains a column named Authors_Email, for example).

I also find that disagreeable. It adds redundancy, hinders readability, and increases the general clutter.

Mike

[Antnee]

Sorry, yeah, I missed the point. Terrible example, I do agree. Over exampled? :|

[Dr John]

I want to throw in my agreement as well, and this gem that arrived in my RSS feed sometime within the last week so you can at least have a laugh of what you might be dealing with if you opted not to give meaningful names to your columns/tables.

Probably made by the same guys who created a database I was asked to help with, which had such user friendly names as col60, col61, col64, col70 etc., a naming convenion they used in several tables in the db. Although there were about half with names like first_name, surname, etc.
Real problem was the likes of col64 etc were almost always boolean !!!