Re: Which database design is better

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 10/04/04 

Date: Mon, 04 Oct 2004 16:42:19 -0700

>> Not here. You shouldn't be using ON UPDATE CASCADE in first place,
since you shouldn't be updating primary keys. <<

Boy, are you in for a surprise when the ISBN and UPC codes go to 13
digits next year! Yes, you can update primary keys because you can
update any attribute; you just need to make sure that all references to
them are also updated.

That was a major advantage of RDBMS over old files systems; identifiers
appear in one and only place, then can be referenced from there. One
fact, one way in one place.

>> Each employee has exactly one store. Therefore store is an attribute
of employee. <<

No. An employee has a relationship with a store; a job title, a
position number, seniority, etc. If it were an attribute it would be a
scalar. Weight and height can be employee attributes; they are scalar
values. Remember that part in the specs in this thread about each store
having one and only one of a certain job title?

If you close a store the employee cannot be in that store. I cannot
close the value "150 pounds" in the domain of the weight attribute.
Very different properties between the entity "store" and the attribute
"weight".

>> That's not a principle at all. <<

Really? What are E-R diagrams based on then?

>> According to that, you could never have a FK relationsip between two
"entity tables", there would always need to be an intermediate
"relationship tables". <<

Unh? What does "between" mean? I can have a reference FROM a table TO
another table -- it is directional, not a bi-directional kind of thing
at all. I

I need to a relationship table if there is a relationship; if there is
just a reference, then I can use a REFERENCES clause.

--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Writing huge Sets() to disk
    ... pushes it's internal dictionaries ... > Almost anything you do copies references. ... > store the values on disc in a reasonably efficient manner. ... bsddb creaps on me that I can store as a key or value only a string. ...
    (comp.lang.python)
  • Re: Implementation of [A]1---->*[B]
    ... So I assume your question is: how does one avoid duplicate references ... if it is considered an error to /try/ to add a participant that is ... of the addresponsibility because it is adding an additional ... A Project holds its own copy of Employee. ...
    (comp.object)
  • Re: OT ~ Happy Days Are Here Again
    ... Every single one of those references go back to the first article in ... White House employee in the meeting told them the President of the ... Not one single source that was actually in the meeting. ... no other source has any references or a name of anyone who was ...
    (rec.outdoors.rv-travel)
  • Re: Leech (?) content of mySQL tables
    ... the foregin key AND referential integrity are not supported in all type ... Create table employee (" ... references employee on delete cascade on update cascade") ## ...
    (comp.lang.php)
  • Re: OT: Job References; are they any real value?
    ... I haven't been an employee (except as a legal fiction for a temp agency, ... I don't like my references to be bothered with a ... give names and numbers of folks I've worked with. ... great programmer... ...
    (comp.lang.cobol)