Problems on using same field names in different tables, specially ID

Question

I am developing a big data base, and from this situation I decided to think always twice at least on making decisions about the table and field layouts. My highest concern is about the primary keys.

I almost always use the table name plus _id to name those indexes. I do this way because when joining tables I do not get to worry about duplicated field names, because Delphi data base components give us the field as table_name.field_name but just the field.

In the other hand, if we could have the same ID field name for every table, it is always easier to create one function to handle every table concerning to that index control.

Does anyone had a bad experience on choosing one of these database designs? Is it a good approach to repeat the name of the table on every field name?

Solution 1

I've designed a number of databases in the past, and made the mistake of inconsistently naming tables and columns more than once. I know that others have different opinions, but these are the rules I now follow:

1. Table names are plural It it feels natural to me to have a products table instead of a product table, especially when it comes to select syntax: SELECT whatever FROM products feels better than SELECT whatever FROM product. In the end it does not matter if you chose singular or plural, as long as you don't mix them.

2. Field names are singular Again, this is a more or less arbitrary choice, but it seems natural to me and is what most people use. (you could argue that SELECT descriptions FROM products makes more sense, but hey, what can't you argue about)

3. All names are lower case and use underscores to separate words Some people use Pascal style capitalization such as ProductId or even JavaScript style productId. I've personally worked with many different database engines in the past, and to be honest I cannot be bothered to remember which ones are case sensitive and which aren't. table_name seems to be accepted universally, and I've never come across any problems with that. Since you're specifically designing a database for a Delphi app, you might want to choose Pascal style instead so that the source code doesn't mix different styles. I personally just stick with field_name. (By the way: even though some database allow special characters in identifiers, I try to avoid them like the plague, same is true for column names that might be a database keyword. Even if I designed a database for a table manufacturer, I would never name the product table "tables")

4. Primary keys and foreign keys include the table name The products table gets a product_id primary key. Whatever other tables link to that get a product_id foreign key. This automatically documents part of the database design, and it allows the use natural joins. I did design a database in the past where every table had a primary key labeled "id". That did get pretty messy very quickly.

5. Other columns don't get their table names prefixed This is probably the rule on this list I do break every once in a while, but first things first. By this rule, the product's description will go into a description field, and not into product_description. The main reason for this is less clutter in the code. Queries get shorter and are easier to read. SELECT product_id, description, price FROM products WHERE price>1 AND price<9 is way easier to read than SELECT product_id, product_description, product_price FROM products WHERE product_price>1 AND product_price<9. Both queries would do the exact same thing, but one spans more lines of code than the other. There are three reasons I sometimes break this rule:

   a. For primary keys. See #4 - being able to see which columns is the primary key of what is not clutter, but important enough to write down every single time.

   b. When two totally different value types would otherwise share the same identifier: a person's name is something entirely different than a product name. At some points in development it makes things easier to be able to distinguish between a person_name and a product_name without handing over tons of metadata. (Or, for that matter, between the person's name and the product's brand_name, which eliminates the need for a table prefix.)

   c. If two tables, both containing the same field name are likely to be joined often in queries. If you have a product's description and an order's description, you might have to rename queried columns a lot, which can be messy.

Again, all of the above are personal preference. Your mileage may vary. The only thing that seems to be universally agreed upon is: Choose one style and stick to it.

P.S. I might not have answered part of your question, but "... it is always easier to create one function to handle every table concerning to that index control." made no sense to me.

Solution 2

I think it's mainly personal preference and amount of typing you like to do and when. Typing all the field names as table_name.field_name so you can perform a SELECT * in a join statement with out having to prequalify with the table name on duplicate field names seems like a lot of typing up front for the few times a field might need to be prequalified and if most of your select statements include the field names then having to type table_name.field_name for just the field seems like lot of typing.

I am sure adding longer fieldnames has insignificant performance issues, and probably only really noticeable when schema information is transmitted across a network for ODBC but still not a real issue.

I don't know about Delphi, but in .NET you can find field names by position also, or by accessing the fields that are flagged as primary key fields so I am not sure about the one function argument being valid for requiring tables to have the same ID field name.

I have always kept my primary key field just called UID in my tables and not included the tablename in the field name declaration and I have never had a problem other than having to prequalify UID fields select statements across multiple tables. Although I can see some benefit in including the tablename.UID for key fields as a lot of SELECT * statements across multiple tables would then not have a duplicate field errors, unless there was duplicate data in the other tables (which I would hope not).

Solution 3

One more thing to consider is database modeling. The tools such as ERwin, Visio etc. migrate the parent PRIMARY KEY down the relationship automatically, so if the field was prefixed in the parent table, you don't need to rename it in the child¹. Since there is no renaming, and no danger someone will forget to do it, just glancing at the field's name allows you to confidently see where it came from, without the need to visually follow the relationship(s) to the field's origin. This is especially important when there are multiple levels of relationships and makes the ER diagram much more readable, IMO.

It also makes the SQL clearer to me since my brain doesn't need to "parse" the aliases to figure out which field came from where, and makes NATURAL joins a bit less dangerous², but other people might have different opinions on that...

---

¹ If you use parent.id and child.id, then you must relame the migrated key to something like child.parent_id to avoid the naming conflict. But if you use parent.parent_id and child.child_id, you don't have to do anything - the migrated key is automatically named child.parent_id. The chain continues with grandchild.grandchild_id etc...

² Through in all honesty, you should never do a NATURAL if you can help it - always specify your join criteria explicitly.

Comments

[axawire]

Thinking about it I did have a problem one time with a UniData ODBC driver ending up having a limit on the length of the query string you could submit, it was limited to less than 256 characters and it was too limiting for me to include all the field names I wanted in one query and those field names were not prefixed with table names in the field name.

[NaN]

That's true. It is why I only call procedures from my applications nowadays. I never do a SELECT in my code, but only inside the procedure.