Re: One-To-One Relationships

From: David Cressey <>
Date: Sat, 01 Dec 2007 11:28:38 GMT
Message-ID: <Gzb4j.729$T41.659_at_trndny01>

"paul c" <> wrote in message news:e634j.80476$cD.6909_at_pd7urf2no...
> David Cressey wrote:
> > "Marshall" <> wrote in message
> >
> >
> >> Some time back someone proposed "people understand tables
> >> just fine" as a law.
> >>
> > I remember that, and I don't agree. While people understand individual
> > tables just fine, they don't understand schemas made up of tables
> > completely enough.
> >
> > In particular, their understanding of how foreign keys work is hazy and
> > incomplete.
> >
> > If you have a many-to-many relationship, like students enrolled in
> > it's easy to state the relationship in words. It's also easy to express
> > an ER diagram: you just put crow's feet at both ends of the line.
> >
> > But it you are expressing the same concept using tables, you now have
> > put a third table in between the students table and the courses table.
> > Then, most of the time, you have to explain why a third table is
> >
> > It gets worse when there are "camps" among the stakeholders about
> > the relationship is many-to-one or many-to-many. An example is matrix
> > management versus conventional management.
> >
> >> It's easy enough that pretty much anyone can learn how to
> >> do it fairly well, directly. So I see no point in any further
> >> methodology.
> >>
> >
> > Based on my experience, I disagree.
> >
> >
> From the general thoughtfulness of your posts, I suspect that any such
> exercise you were involved in was deemed successful by the stakeholders.
> From what I've seen, you've made the best practical arguments for the
> workability of ER, much better than any paper I've seen about it.

Yes, that's right. It was a useful tool for giving feedback to some important stakeholders, without getting them involved in database design.

> But the flexible thinking you display isn't wide-spread. This is why
> I'm sceptical that your experience applies in general. In the end, what
> spoils it for me might be the first ER practitioners I encountered who
> would present me with a fait-accompli design and yet not be able to
> answer specific questions about what various system functions were
> supposed to do. So I'd look into those and find that all the business
> data wasn't described in the model. I don't think those people were
> stupid, it was just too easy for their method to bypass reality (seems
> ironic when reality and entity are so often mentioned in the same
> breath). At the time, I used to ask them: how can I execute your model?
> They would tell me I needed to transform it and execute something else.

I was a bad user of the ER tool for quite a while, before I became a better user of it.

When I was first exposed to ER, I thought of it as a "watered down relational model". That isn't what it is, and that misrepresents what it's for.

I'm not sure what you mean by "fait accompli design", but I'm going to give several responses. First, ER isn't a design model. It's an analysis model. Anyone who uses ER as a tool to explain the design is using the wrong tool for the wrong purpose. If you based the design on relational tables, and you want to explain the design, use the relational model for that.

Next, "fait accompli" assumes that analysis is complete before design begins. That, in general, turns out to be false. In particular, it takes several iterations of producing an ER model, and getting it validated, to uncover the existence of a lot of "things" and "relationships between things" that the users of the information constantly talk about with each other, but are not acknowledged at all by the formal systems they are forced to use. This perpetuates the analysis flaws of previous systems, unless uncovered.

Next, modifications to an ER model should be inexpensive to propagate to a relational model and thence to a create script written in SQL DDL or some suitable successor. That's where tools like Data Architect came in so handy. It would do the grunt work, and save me from a lot of time, effort, and mistakes.

Finally, there's a time and a place to be flexible, and there's a time and a place to be rigid. When performing analysis, flexibility helps you find out what the other people are really thinking. When approaching the end of design, it's time to get uncompromising with the design, especially if it's your own design. You need to know whether, if this thing gets built, it's really going to work. Too much flexibility is bad at this point.

Bottom line: too many people confuse analysis and design. Received on Sat Dec 01 2007 - 12:28:38 CET

Original text of this message