data modeling questions

wasabi3689 used Ask the Experts™
I have a general question about data modeling. I need a answer based on your experience.

I am going to create many data models for the company

I want to know how you approach this in theory and in practical way step by step and what tools do I need to use?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
First question is do you have a budget to purchase a tool.

The most popular in the industry are CA ERwin, Embarcadero ER/Studio and Sybase Power Designer

There is also Toad Data Modeller, Model Right, and misc other cheaper tools, including Visio Enterprise, but the above are the corporate, enterprise favorites. A lot of the smaller / cheaper tools lack support for certain database objects, scripting, etc.

From experience, I moved from ERwin to ER/Studio, but if I could afford both, I'd add Power Designer.

In any case, you will want to keep your models under revision control. Each of the ones I mentioned first have a model repository as an add-in that cost more $$, but I have found that using Subversion or Kiln works just fine.

Try to learn to deal with Logical models and Physical models. ER/Studio and Power Designer help you easily maintain multiple physical models for the same logical model, which is critical for cross-platform databases.

Other than that, for existing databases, you'll be reverse engineering to start a model. Buy a book on data modelling is about all I can suggest without more specific questions.

Regarding the modeling approach:
-The "normal form" and BCNF theory are important to know but are more guidelines than rules
-Having tons of empty columns in a table are not a problem as of you can have one single table to handle ALL "people" related mono-valued information
-If you expect major criterias to be used on TWO tables at the same time, ask yourself about a denormalization on those criterias (Ex: Select Where A.Type = ? and B.Class = ? can't be indexed ... and managing to copy, using triggers for example, A.Type to B.A_Type allows to index on B[A_Type, Class])
-think of your tables usages : the more dense they are, the fastest they are; having large updates on some fields may be better served by adding a "supplementary fields" table that handles those size-moving fields and can be re-organized alone without perturbing the primary table
-do not mix in the same "generic" field, values that has very different statistics (Example: Field "Num1" may, depending on a Type field in the same record, contains an ID that is almost unique or a "Priority level" that is not a good search criteria at all)
-some basic fields can be considered mandatory : ID, timestamp_last_updated, timestamp_begin, timestamp_end, version
-using begin and end timestamps allows to have duplicate ID with different handles Ctrl-Z operations and super-simple archive/purge modes

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial