Best Practice Guidance for Data Modelling
By : Kasim Wirama, MCDBA
Good database design should be come from good data modelling. A DBA should have knowledge for data modelling when he designs database. There are some methodologies regarding to data modelling, they are Information Engineering (IE), Chen ERD, generic ERD, and IDEF1X. You can implement generic ERD by using CASE tools such as Microsoft Visio. This article, I would like to give some best practice regarding to design data modelling elements.
Data modelling process have steps from user requirement gathering, from this you sketch to conceptual data modelling, then move to logical data modelling and implement into specific database vendor by creating physical data modelling.
When you are in creating conceptual data modelling, you create entity, its attributes, relationship to other entities, attribute domain, and objects. Here is the best practice for each of items below.
For entities, sometimes a DBA faces sorts of naming convention whether it should be plural or singular form. Actually it’s no problem whether it is plural or singular as long as you are consistent. I prefer to give singular name, because it represents single instance for each row.
For primary/foreign key, it is confusing when you give attributes name same to its entity name. so it’s better if you want to let user be able to identify it by adding some prefix, for example entity name Customer, you give its key attribute as CustomerId.
For relationship name, it is better to give verb-name to explain relationship between 2 entities, but don’t confuse to your user with technical name that describes cardinality and database relationship (one-to-many) because conceptual diagram depicts high level from end-user view, the conceptual data modelling is not intended for developer.
You need to define attribute domain to maintain consistency across database and it can be used as template when you design other databases.
Make sure you define comprehensive objects from user requirement gathering phase to avoid possibility of changing database design in the middle of development phase. It is much better to confirm to your end user with your objects and its interactions to make your database design covers requirements that end-user will expect.