I thought I would spin off an earlier topic along these lines on programming.

First,

An ENTITY is a person, place, thing, event or concept about which we collect information.

This is the primary academic question of data modeling interviews.

We can get into more philosophical aspects of data modeling as well.

Now for the survey. A definition of terms.

Note that all of the following are Data Models.

1. An Entity Relationship Diagram (ERD) only shows entities and relationships between entities. It is the simplest logical model.
   
2. A Key-Based Model shows the entities, their primary keys, alternate keys, foreign keys and the relationships between entities. It is the intermediate logical model.
   
3. A Fully Attributed Model shows entities, keys, non-key attributes and the relationships between entities. It is the final logical model.
   
4. A Physical Model converts entities to tables, attributes to columns and applies the constraints of the host RDBMS to the Logical Model.
   
5. The Database Schema is the implementation of the Physical Model on the host RDBMS.

I hope the survey can guide our discussion somewhat.

very nice categorization

the first step is nice, but really rather just like running up and down the scales a few times before the actual performance

the term "key-based model" is new to me, but that is where all the blood and sweat must go, that is where the ultimate success or failure of the entire project is determined

steps 3, 4, and 5 are trivial, to be delegated to backroom DBAs after the dust has settled as to what really matters

note that only after step 3 has been completed should any application programmers or user interface designers be allowed to commence work

and to bring this back on topic in keeping with the subject of this thread, what should data modellers learn first? heck, that's easy, they should learn what first normal form is



rudy
http://r937.com/

Edit: the following in response to r123456's post, which seems to have disappeared

my understanding is that surrogate keys are assigned only in step 4, physical model

could be wrong, though (wouldn't be the first time)

note surrogate key is not the same thing as alternate key


rudy

Hi,

I removed the post as I thought it might of been off topic, however I'll add some insight.

I agree with what you said about surrogate's and the physical stage, this is what I believe though thought I should check with others. This seems like an appropiate time to mention associative entities, whereby a new entity is formed on the basis of the M:N relationship. This would be the case for customer orders product. As the primary key's of these 2 entities cannot identify an order uniquely then the new associative entitiy is formed on the logical mode containing order_id at the minimum as its key attribute.

I like what r937 said regarding running up and down the scales with the models. I tend to regard the logical data models (1-3) as open ground for as many iterations as I want. Then when I am ready I cross over into Physical realm (4) and close the gate behind me. If I make changes I am now making changes based on the constraints of the RDBMS I am designing for. However, if the physical modeling team finds a glaring logical error, of course I will make an exception and go back to make the correction. This ensures if I change platforms and use the same logical data model it will not require the new physical team to rediscover the logical design error.

I want to point out that my categorization is borrowed from the Zachman Framework and the work of Thomas Bruce.

http://www.zifa.com

http://www.amazon.com/exec/obidos/tg...12635?v=glance

wow, zachman -- brings back old memories

zachman, finklestein, and ross

i ran into a web site the other day, ross is still doing the same thing as twenty years ago too -- http://www.brcommunity.com/

i remember getting a data modelling newsletter (in the mail) from him long, long before there was a web...


rudy

Some good ideas take time to be accepted. When it comes to relational Zachman's work still holds. It is the Object world that is different. The object world also pushed relational notation to be more communicative than IDEF1X. Check out Visio's default relational notation (not the ORM models).

http://msdn.microsoft.com/library/de...itectpart5.asp

Note how the key indicators are in their own column. You can also call up your datatypes in their own column. I find it visually much easier to read than the IDEF1X attribute format. In fact you can say that the IDEF1X notation had been normalized.

Hi,

Good Work Yar.

My choice is 3.

So we have two firsts:

1. A full understanding of relational modeling
2. A full understanding of nomalization

There is also a system development lifecycle:

1. Decide
2. Define
3. Design
4. Develop
5. Deploy
6. Employ

and the deliverables a data modeler must produce for each.

For example, I have been brought into Project Initiation sessions to do an ERD to provide an overview of the existing and target data model. That ERD served its purpose because it provided the Client's view. However, smaller projects might require finer granularity. Necessity dictates.

The trade off for databases is definition, manipulation and querying. If you want static or dynamic database design, you have to support definition performance. If you have a transactional system, you normalize to support manipulation performance. If you have a data warehouse, you denormalize to support querying performance. You will always find yourself at some point between the three. Finding that balance is unique to every application.

Balanced performance is ultimately the goal. The best way to achieve a balance is to have quantifiable performance requirements for your definitions, manipulations and queries. They have to be set early on, then you are able to use them as criteria for Administration, Transaction and Reporting decisions. Normalization and denormalization are not goals in themselves, performance is. Data entry contraints are not goals in themselves, performance is. The number of joins for queries are not goals in themselves, performance is.

Performance, perfomance, performance. Know why.
Definition, Manipulation, Query. Know what.
Balance, balance, balance. Know how.

Talking about performance we have to stop and think about performance criteria. In reality, there are few. They boil down to four. And they occur in the same order every time.

1. Capability - Can it be done?
2. Scalability - Can it be done in my environment?
3. Reliablity - Can it be done in my environment without risk?
4. Price - Can it be done my environment without risk at the lowest cost?

The list was the conclusion of research on a decade of harddisk technology data in private industry. As each criteria is met it is more likely that there is a package out there that can do it cheaper than you.

When we are designing a database elements come into play in a logical model that have not been given adequate emphasis in the past. The key element is the needs of the user.

Alan Cooper recently came out with a new book, "About Face 2.0" and challenged one of the prime misconceptions of database designers. He stated that you cannot simply place a form on top of each table in a fully normalized model and expect it to be a fine user interface. People do not work that way.

Normalization is not the end of a logical data model. Business rules based on anticipating user interaction needs also plays a key role.

Here's a link: http://www.cooper.com

I haven't read this book, but I don't see what user interfaces have to do with logical database design, really. Of course, simply providing a user interface structured on the basis of the normalised tables may not be good enough. A combination of views, stored procedures and client application may be required to present the data in a user-friendly way. But surely no one is suggesting that the logical database design needs to be altered to make this happen?