Child pages
  • Database field naming ideas
Skip to end of metadata
Go to start of metadata

Imported From: http://groups.google.com/group/in-portal-dev/browse_thread/thread/241a5f6370ded07c#

Database is one thing In-Portal can't live without. That's why it's important to keep it as clean and understandable as possible, especially during times, when we constantly adding/removing fields/tables from it.

Recently I needed to search for "Type" field I added to one of the tables. I was very surprised to find, that a lot of database tables share exactly same field name resulting in a lot of results and no way to automatically find all needed usages.

Right now we use pascal case scheme in naming tables and fields in database, e.g.

  • Users
  • UserCustomFields
  • PortalUserId
  • Username

This works perfectly, but for fields with more general names, like "Type", "Status" and so on I'm proposing to add something specific to identify connecting with corresponding database table.

For example:

  • Type for users will become UserType
  • Type for orders will become OrderType

Following this logic CreatedById field should be renamed to OrderCreatedById, AffiliateCreatedById and so on, but I'm not sure this is good idea, since we don't rename or refactor these fields that much.

5 Comments

  1. at least this would help to read DB by humans...

    Envoy

  2. Alex,

    Thanks for bringing this up to our attention.

    Yes, I agree we should name Type fields more specific to it's use. In my
    opinion OrderCreatedById, AffiliateCreatedById it unnecessary
    since CreatedById always mean the same thing in one or multiple tables.

    I think the rule should be - field name must be specific enough to describe
    it's purpose and be unique within the same database table.

    Thanks.

    DA

  3. Should we locate all Type fields across all database and rename them in
    5.3.x?

  4. Yes, I would say so. It will set a good tone for future development. As a
    matter of face Type is reserved word by MySQL so it will make even more
    sense to do this :)

    Thoughts?

    DA

  5. Completely support that idea. Let's create a task then.

    Renaming Type field is pretty straight forward:

       1. take table name (e.g. "Agents")
       2. convert it to singular form by removing "s"/"es" (e.g. "Agent")
       3. append field name (e.g. AgentType)

    Pretty easy to perform, but we must create corresponding upgrade scripts to
    keep db structure consistent between upgrades as usual.