1

I'm trying to learn some basic database design through open source projects.

And look into the WordPress database schema, the posts table for example, you'll find something like this

post_author
post_date
post_title
post_content
...
...
etc.

We're already in the posts table, why not just author, date, title, content? Is there any reason? Or just because WordPress is too old..?

(The same in other tables. In users table, will find user_login, user_pass, user_email, etc.)

I found many people say that using a column prefix is a bad practice, so, just title, content... will be a better design?

2
  • its not old... most probably table name will be used initially for all the fields..
    – Karthick
    Aug 12, 2014 at 9:45
  • A column prefix is a good idea when you have information from two "domains" in the same table, but in this case I think it's just ugly. "Post" is already implied by the table. Aug 12, 2014 at 10:29

2 Answers 2

4

There is more than one school of thought concerning the naming of columns or fields.

One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found. Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, but that it's the date of a post or a comment.

One advantage of this convention is that it makes it easier to maintain a data dictionary, where the full definition of each data element is to be found. It's easy to navigate from a column name to the corresponding data dictionary.

This convention was apparently used in Wordpress.

Another school of thought says that the name of a column or field ought to convey the intent of the column or field in the table or record where it is found, according to the role it plays in that context. Thus, date merely tells you that it's a date, but post.date presumably tells you that it's the date of a post. And comment.date would tell you that it's the date of a comment.

There are advantages and drawbacks to both schools of thought. The most important thing is to be consistent, once a naming standard has been agreed to. A naming convention that is sometimes disregarded creates pitfalls for the maintainer.

One drawback to the second convention is that FKs can generally never have the same name as the PKs to which they refer. Another drawback is that, if there is a context, such as the forms manager, in which "date" is a reserved word, now you're forced to violate your own convention.

One drawback to the first convention is that it makes it awkward to express a reflexive relationship, one in which an FK refers to a different row in the same table. Take, for example, "supervisor_id", a column you find all the time in employee tables. You can't give it the same name as the PK that it refers to, because then you would have two columns with the same name in the same table, and that's forbidden. So now you're forced to violate your convention.

2

A very basic answer would be to let the developer know what exactly they're working with without needing to assign aliases in the event more than one table is being used.

For example, if you're joining posts on comments (I'm not familiar with WordPress but just as an example), and the posts table as well as comments table have the columns title, author, date, and content, the result will be much easier to use and you'll have a cleaner query as opposed to having to add aliases to know what is what.

So basically, you'll have an output with (when joining the tables):
post_content, post_title, comment_content, comment_title, etc.

Instead of:
content,title,content,title

And obviously this JOIN example is just one case where that naming convention is better, there are pros and cons to everything, even database normalization has cons.

2
  • "content,title,content,title" will not work, but "posts.content,posts.title,comments.content,comments.title". I think it is not possible to convince me that this is much harder to use than "post_content, post_title, comment_content, comment_title"
    – miracle173
    Aug 12, 2014 at 12:41
  • Preference. I didn't develop WordPress, I'm giving an example of why someone might go this route. Just because you don't see why someone would prefer a method, doesn't mean the method is invalid.
    – Ryan M
    Aug 12, 2014 at 12:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.