Database field naming ideas

[Alexander Obuhovich]

Database if one thing In-Portal can't live without. That's why it's import 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.

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

[Phil]

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

Envoyé avec Sparrow

[Dmitry A.]

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

[Alexander Obuhovich]

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

[Dmitry A.]

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

[Alexander Obuhovich]

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.