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

databasedatabase-designdelphiindexing

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?

Best Solution

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.

Related Question