Re: multiple cascade paths

On Mon, 11 May 2009 10:00:38 -0700 (PDT), --CELKO-- wrote:

If with "relationship among personnel", you mean a binary relationship with both roles played by an employee, you are obviously wrong. <<

Actually, you just asked what was wrong wiht the skeleton schema in
your posting.

Hi Joe,

Here is an exact copy of the first few lines of my post:

(start quote)

I hate the multiple cascade paths error. <<

You should not have them if your schema is designed properly.

Hi Joe,

You're spouting rubbish again. Please point me to the design flaw in
this schema (actual schema would have more columns, of course):

(end quote)

The context of the discussion was clearly the multiple cascade paths
error. I even quoted the relevant bits back. If that isn't sufficient to
make you understand that I tried to show that a proper design can have
multiple cascade paths, then shame on you for not properly reading my
post. (And if it is, then shame on you for pretending not to understand
it).

And what is the rule when we are studying ER modeling and diagrams?
Entities and relationships go into separate tables. That is why
entities are shown with rectangles and relationship with diamonds.
Etc.

LOL! I think I've seen about as many rules as I have read books. :)

One quite common rule is to combine all column with the same functional
dependency in a single table. That has the advantage of keeping the
number of tables low and keeping all information about the same entity
close together.

I've seen the rule you quote a couple of times, though so far nobody has
ever been able to explain to me the advantage of having seperate tables
for your one to many relationships. (Seperate tables for many to many
relationships are a given, of course).

A third option, endorsed by the people behind the third manifesto, is to
use seperate tables for each OPTIONAL attribute or relationship, though
MANDATORY attributes (and, depending on who you ask) relationships can
still be combined if they have the same functional dependency. This has
the advantage of avoiding NULLs in storage, which is great if you share
the TTM's dislike of NULLs and yet are prepared to accept that they'll
return once you start outer joining these tables. Performance tends to
suffer from the vastly increased number of tables (and hence, the much
larger number of joins required). I dislike this version, because (a) I
have nothing against NULLs, and (b) it seems more of a stop-gap solution
than a really structural approach because of how the difference between
optional and mandatory changes how attributes (and relationships) are
treated.

A fourth option, very rare, takes the TTM approach a step further by
asking for seperate table for each attribute and each relationship. For
those who use NIAM and/or ORM (Object Role Modeling, not that other
ORM), this might at first glance seem interesting because it produces a
one-to-one match between their model and the implementation, but even
though I am one of NIAM's biggest fans, I'd never endorse using such a
database design. For the simple reason that performance with the data
scattered over so many tables would suck. Even worse than the TTM
version, which is already pretty slow.

There are of course other variations, but these four seem to be
prevalent. And since the third and fourth option are to slow and nobody
has yet been able to explain the advantages of the second option to me,
I always go for minimizing the amount of tables by using the first
option (with some exceptions to the rule, such as subtyping or vertical
partitioning when there's a large number of optional columns in a table
with lots of rows).

Hmmm, time to stop ranting and get back on topic.

I never used a "dept_nbr" in my schema. I had a "dept_code", which is the four-letter code that the sample organization I invented for this schema uses instead of always writing out the full department names. <<

A code is a standard set up by an external (often legal)
organization. I would not be the identifier of an entity because more
than one entity can have an attribute with that value. But if your
industry uses the term "dept_code", then go with it.

How many industries have you been to where the company goes to some
external organisation and asks them to set up a standard for naming
their departments?

I had no property to model "being the department boss". I did have a property to model a department "being led by" a particular employee. <<

That is clearly not a property, but a relationship.

My bad, should have used the term relationship. But at least you now
understand what the column is used for. (Do you?)

Also, I never mentioned job assignments in my example. Don't introduce new concepts. The company I invented for this example is quite small and informal and doesn't want to work with job assignments. An employee is assigned to a department and works there, that's all. With "department boss" being the sole exception, that role is specifically given to one employee. <<

Actually, you did by hiding it in a normal form error with you
"department boss" column. Remember the classic normalization example
where we start with a single table that has {students, teacher,
course, classroom} all together? First the repeating group of student
is split out as a class roster so we are at 1NF, and eventually we
have a schema with teachers, courses, classrooms and a schedule among
them

I even remember sending you a mail pointing out a flaw in your
discussion of that example in one of your books. Though that is years
ago now, so it's probably corrected in current editions.

But I fail to see the relation here. Each department has exactly one
boss, so boss is functionally dependant on department, not the other way
around. That means that there has to be SOME table with the identifier
for departments as the primary key and a non-key column that references
the employees table for storing the fact that employee X lease
department Y.

Calling something a normal form error is easy. Pray tell me WHAT normal
form you think I violated, and why.

No, you have added your own interpretation to the terms I used instead of first checking the roles in the relationships and then based on that deducting the actual relationships. <<

How unreasonable that when I am asked about fish swimming, I assume
water :).

Except I just asked about fish, and you added the swimming first, and
then went on to add the water.

It seems reasonable that if someone is a boss, then there is an
organizational structure that establishes that relationship. It seems
reasonable that "boss-hood" is a property of a position within the
organizational structure, regardless of who holds that position.

Reasonable? Hmmm, yeah. Maybe. But that's largely irrelevant. I did not
ask you to assess whether my design missed anything that you could
reasonably assume to be required as well.

You seem to assume that being a boss is like having eye color, mass or
other attributes that innate to the entity. Some employees are
created with a "boss-hood" attribute that has no context. Kind of like
super powers, I guess.

No. I just assume that if a paying customer assures me that the only
information they require is of the kind "John is the boss of the Sales
department", "Mary is the boss if the Accounting department", and "Mary
is (also) the boss of the Helpdesk department", they expect me to build
them a database to store exactly that and nothing more, and to bill them
for that and nothing more.

Why would I force a complete job hierarchy on them if they've been doing
fine as a small, informal company for three decades? I'm a database
expert, not a organisation expert. I'm sure that once they get too large
for this informal structure, thhey'lll be able to hire advisors who
specialise in this stuff and whose advise should be much better than
yours and mine combined.

(snip)
Your mode allows an employee to work for multiple departments or for none at all, and also allows a department to be led by multiple employees or none at all! <<

If I had better specs, we can handle those things with UNIQUE
constraints on the relationship tables in my model. I left the
skeleton schema as flexible as possible until i could get.

I though my DDL showed pretty clearly that an employee has to work for
exactly one department. After all, there was a foreign key to the
department table in a mandatory column of the employee table. But with
hindsight, I'll admit that posting the specs in the form of DDL and then
asking you to point out the errors in the design got you in a bit of a
circular problem. That's why I restated the specs in a different form
(SBVR-Structured English) in my previous posting. Even if you don't know
this OMG standard, they should still be easy to understand for anyone
with a working knowlegde of English (which is one of the biggest
advantages of SBVR-Structured English).

Too bad you chose not to reply to that part of my message at all. Was it
that hard to come up with a properly designed schema to implement the
specs without running into the multiple cascade paths problem on SQL
Server? And was it that hard to admit that you jumped the gun much too
quick when you told the original poster that the mere fact of running
into the multiple cascade paths indicates unproper design?

You have said that "you should not have (the multiple cascading paths error) if your schema is designed properly", implying that a properly designed schema always manages to avoid this error. Now put your money where your mouth is and prove it. <<

Some of the tricks for working around SQL Server's overly strict
implementation is in another posting. This is one reason I like DB2
over SQL Server. Somethings you can work around, somethings you are
stuck with in every product.

I didn't ask you for tricks to work around SQL Server's limitations. I
am a SQL Server MVP, for crying out loud - you can safely assume that I
know how to work around SQL Server's limitations.

I asked you to post a proper design for the requirements given that
doesn't need these workaround. Your first post in this thread clearly
states that "you should not have them (multiple cascade paths -Hugo) if
your schema is designed properly". Thus implying that for any set of
requirements, when implemented with a "proper design", the multiple
cascade paths error will not apply.

Let me now if the requirements are too hard for you, or if you have real
trouble reading and understanding SBVR-Structured English. I'm sure I
can come up with an even more simplified example, of with a different
way to state the requirements.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.

Relevant Pages

  • Re: string from column value
    ... Pro SQL Server 2000 Database Design ... What is wrong with the schema? ... >> Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: same application on multiple schemas
    ... As for the "comparison" with Sql Server, it was not intended to ask ... why Oracle does "not" support sql server behaviour:) I know oracle (I ... For every customer that will use our application, ... will use schema "SchemaA" ...
    (comp.databases.oracle.server)
  • Re: Model Driven Architecture, bussiness rules in DB?
    ... authorization schema. ... neither does SQL Server. ... For instance when you grant access to a table you should be able ... accessing applications? ...
    (comp.databases.ingres)