开发者

How do you handle descriptive database table names and their effect on foreign key names?

I am working on a database schema, and am trying to make some decisions about table names. I like at least somewhat descriptive names, but then when I use suggested foreign key naming conventions, the result seems to get ridiculous. Consider this example:

Suppose I have table

session_subject_mark_item_info

And it has a foreign key that references

sessionSubjectID

in the

session_subjects 

table.

Now when I create the foreign key name based on fk_[referencing_table]__[referenced_table]_[field_name] I end up with this maddness:

fk_session_subject_mark_item_info__session_subjects_sessionSubjectID

Would this type of a foreign key name cause me problems down the road, or is it quite common to see this?

Also, how do the more experienced database designers out there handle the conflict between des开发者_如何转开发criptive naming for readability vs. the long names that result?

I am using MySQL and MySQL Workbench if that makes any difference.

UPDATE

Received the answers I needed below, but I wanted to mention that after some testing, I discovered that MySQL does have a limit on how long the FK name can be. So using the naming convention I mentioned, and descriptive table names, meant that in two instances in my db I had to shorten the names to avoid the MySQL 1059 error

http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_too_long_ident


Why do you care what the FK names are? You never see them in code or use them. We also name our tables quite descriptively and commonly have names like this, using SQL Server. It doesn't matter to us, because we never seen them. They are just there to enforce data.


FK names are important for maintenance. Generally I only refernce the FK and the two table names, not the fields in the names. If you have named your fields correctly, it will be obvious what the fields are.


Although it probably makes no difference. I will say that i've had table names both ways. And in my opinion using long descriptive table names is overkill, and when working in code or even at the command line these long table names become burdensome and tedius. I mean seriously, who in their right mind would have a nearly 30 character table name, ie. stationchangelogmasterreport. Now imagine tens or even hundreds of these in a database system. from a developers point of view, this is just dumb!! My recommendation... put some thought into it, use abbreviations (when you can) and keep it short and to the point. for example, the above table name could be shortened to: stnchangelog, and if someone absolutely NEEDS a huge description explaining every meaning and use case for the table, then put this description in the table metadata, ie. the comments on the table. This keeps us developers from going crazy (and hating you for it), and offers the "meaning" of the table if needed.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜