Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Question about naming columns

    Hi all.

    I've got a question regarding naming conventions in database design.

    Say that I have the following 4 tables in my database.

    Articles(ArticleID ,Intro, ArticleText, CategoryID)
    ArticleCategories(CategoryID, CategoryName)

    Products(ProductID , Description, CategoryID)
    ProductCategories(CategoryID, CategoryName)

    These tables represents Articles, where all articles has an ArticleCategory, and Products where all products has a ProductCategory.

    Is it wrong (it works from a technical point of view) to reuse column names over different tables, like I have done in table ArticleCategories and ProductCategories.

    I'm asking becase I like to follow coding standards. Makes it easier for other people to understand my work.

    Cheers

    Stian

  2. #2
    Join Date
    Feb 2002
    Posts
    10
    I guess the question boils down to whether or not the name of the column needs to be descriptive out of the context of the tables in which it belongs

    stian

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Question about naming columns

    I would prefer to distinguish the different columns. I also like the same column to have the same name in different tables, as far as possible.
    Some justifications:
    1) Impact analysis: identifying where in your code a particular column is used. (My current client actually likes to make EVERY column name unique for this reason, but I would not normally go that far).
    2) SQL Join syntax: the JOIN USING and NATURAL JOIN syntax expects the join columns of 2 tables to have the same name.

    So I might choose the following names:

    Articles(ArticleID ,ArticleIntro, ArticleText, ArticleCategoryID)
    ArticleCategories(ArticleCategoryID, ArticleCategoryName)

    Products(ProductID , ProductDescription, ProductCategoryID)
    ProductCategories(ProductCategoryID, ProductCategoryName)

    ... maybe with some standard abbreviations like ProdCat to shorten them.

    The main thing is that you should have a standard, and stick to it. What I really hate is when related columns have names that are nothing like each other, e.g.

    WHERE project.manager_code = employee.emp_id
    AND project.start_date <= project.dt_finish

  4. #4
    Join Date
    Feb 2002
    Posts
    10

    Re: Question about naming columns

    Thanks mate

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Stian

    Great question and intent. Here are my views on correctness in ER design and my standards that relate to your question. These are intended to enhance (not replace) SQL capabilities and to enhance readability & understanding.

    1 The full address of a column is [[dbname.]table.]column, you only need to specify enough to make the address identifiable. Refer any SQL generated by a tool or in unaliased joins (eg. follows). Therefore Andrew's client is incorrect, there is no need to bury the table name in the column name, it is superfluous and limiting, it will lead to very long column names. Clarity:
    Code:
    select
        Product.Code,
        Product.Name,
        Product.ProductTypeCode,
        ProductType.Name
    from Product, ProductType
    where Product.ProductTypeCode = ProductType.ProductTypeCode
    2 Columns should be consistently named throughout the db.
    2.1 The same column should not have different names. Now this only appears in the context of relations (otherwise it would not be the same column), ie. PK in the parent and FK in the child. Therefore every PK should have a unique name across the db and it must be named the same wherever it appears (ie. as an FK).
    2.2 Different columns should not have the same name. Collolary to 2.1, no non-key column should have the name of a PK. Every FK must have a PK column name.
    2.3 This is not relevant to non key columns, so it is acceptable (and more readable) to have (eg) a short description column in two related or unrelated tables that are both called Name.
    2.4 We should standardise here as well, so we should not call anything other than a short description Name, and we should not call a short description and a long description Name.

    3 The singular form is used in all naming conventions.

    Finished eg:
    Code:
    ArticleCategory(
        ArticleCategoryID, 
        Name)
    
    Article(
        ArticleID,
        ArticleIntro, 
        ArticleText, 
        ArticleCategoryID Foreign Key ArticleCategory.ArticleCategoryID )
    
    ProductCategory(
        ProductCategoryID, 
        Name)
    
    Product(
        ProductID, 
        Description, 
        ProductCategoryID Foreign Key ProductCategory.ProductCategoryID )
    4 In a well-defined database, every PK (and therefore every FK) has its own user defined datatype. It eliminates join coding errors (datatype mismatch) and assists in table administration (change, growth).

    HTH
    Derek
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Feb 2002
    Posts
    10
    Thanks for that thorough explanation.

    It is very helpful to read the views and standards of others.

    One more thing. I've finished my first draft of the ER diagram, but I would also like to write a Data Document that describes the database in more details. Do you have any tips as to what should be in there and how it should be structured?
    I'm thinking that I should describe each data object, relation and update/delete/add rules in detail. Do you have standards that you follow or recommend, or any hints as to where I can find information about how to write such a document?

    Thanks for your help

    Stian

  7. #7
    Join Date
    Oct 2002
    Posts
    8
    It really a v good guide on naming....i am wondering how to identify tables by modules..Do U have some solution for that also

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    What do you mean by "how to identify tables by modules"? I don't understand the question.

  9. #9
    Join Date
    Oct 2002
    Posts
    8
    i m talking about a moular beased ERP solution where u can have separate modules of sales, inventory, payroll, ledger etc all integrated.

    wht naming scheme do u suggest in this type of databases

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It's quite common to see short prefixes for tables like 'SAL_', 'INV_', 'PAY_', 'LGR_', to identify major subsystems that they belong to. I'm not advocating (or deprecating) it, just saying that I have seen it done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •