Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: review my schema - and a couple of dumb questions...

    Hi,

    I've just built the attached (and linked) schema for a sort of extranet facility - somewhere that we can upload files to for clients to look at and comment on.

    It's a bit complicated, and I'm not sure I've got it entirely (or even nearly) right. I would mightily appreciate anyone looking over it and adding their comments, particularly with respect to the following questions:

    1. I've linked users to a company, users to projects, and projects to a company. Does this cause a conflict? A project should have a parent company, and it should have users associated with it, but those users don't necessarily have to be from that company. That makes logical sense (to me) but is the way that the relationships are specified cause problems?

    2. I've used DBDesigner to draw this diagram, and I'm not sure if this is a software-specific question or not, but every single one of the relationships I've added is a 1:n Non-Identifying Relation. Firstly, despite re-reading the help file many times, I can't figure out what the "Non-Identifying" bit signifies. Secondly, it worries me that all these are the same. Should I have used different relationship types in some cases? I'm looking suspiciously at the Users/Projects joining table here.

    3. This is probably the really dumb question, but I'll ask it anyway: why do I need to define relationships between tables? Say I write the following to get me a list of file urls and titles:
    Code:
    SELECT studioFiles.file_url, studioFileTitles.title FROM studioFiles INNER JOIN studioFileTitles ON studioFiles.file_title_id = studioFileTitles.id
    ... then I've defined the relationship between those tables with the JOIN, right? Why does the database care that there's foreign keys and stuff set up?

    4. Yes, this is the same project that I was clearly having problems with on this thread. If anyone fancies having another go at answering that one with respect to this schema, I'd be ever so impressed.
    Attached Thumbnails Attached Thumbnails studio_schema.png  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, sorry, it seems i've left you hanging on that other thread, there was some more work to be done on that query

    but let me answer your questions here first

    1. it's fine

    2. "non-identifying" means that the FK is not part of the PK

    3. the database cares--and so should you-- about foreign keys because the enforce relational integrity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Ok, thanks

    So:

    1. Phew

    2. Umm... ok. I think. Your article discusses this a bit and I think I get it. I'm dimly aware that PK's can consist of combined fields, or something, but I've always simply given everything an ID field. I know where I am with an ID

    3. Yeah - again, your article was a great help. I guess what vexes me is that I've got to specify the relationship twice - once when designing the database, and again when I'm writing SQL code that explicitly defines the same relationship.
    Looking at my example the other way round, for instance, why can't I just say...
    Code:
    SELECT studioFiles.file_url, studioFileTitles.title FROM studioFiles INNER JOIN studioFileTitles
    ? Why do I have to add the ON bit? It knows the relationship exists, it knows I want to join those two tables. Why can't it assume that the relationship I've already defined is the one I want to use? Or can it?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2. see this thread -- http://www.dbforums.com/showthread.php?t=1617206

    quoting myself, "the profligate and unthinkingly wanton use of surrogate keys is the main reason we get so many people posting 'woe is me! how do i remove all but one of the dupes in my table?'"

    3. don't think about your particular tables for this next bit, just think about tables in general...

    you are allowed to join tables on any columns you want

    this is a design spec at the very heart of sql

    okay, how do you feel now about having to specify which columns to join on in each particular query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Well...

    you are allowed to join tables on any columns you want
    Ok, that seems like a bad idea. There, I said it. Conveniently ignoring the fact that all but the most basic SQL discussions go over my head, I'd have to say I look at that sentence and think "why would anyone want to do that?"

    I mean, I wouldn't write a function that takes a bunch of specific parameters and then expect people to sling what they like at it. If I've defined a relationship between two tables, where orders.customer_id relates to customers.id, then I don't want or expect people to start linking orders.deliveryaddress to customers.homeaddress.

    But I guess that doesn't really answer the question. Let's say fine, you can attempt to join orders.date to customers.email - but what if I don't specify what I want to join on? Shouldn't it go looking for a relationship that's already been defined, to save me the trouble of having to explicitly declare one?

    There - ignorance AND laziness in one post

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Spudhead
    There - ignorance AND laziness in one post
    You're nicely "on track" for becoming a DBA!

    There are some products that do exactly what you are expecting, read the DRI and use it when they are either explicitly or implicitly directed to do so. Unfortunately those products aren't SQL, but that's another story.

    While those times are rare, there are times when it actually makes sense to join on something other than a foreign key. In the vast majority of cases, most users want and expect the foreign key to be used.

    I usually use a surrogate key (such as an identity) and also declare a natural key (on the columns that the user sees and uses) too. I NEVER willingly let the user even know that I have surrogate keys, much less show them the values of those keys because what users can see they will eventually want to change. This allows my applications to use the surrogate key freely, but the alternate key keeps the data "clean" for the users too.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I usually use a surrogate key (such as an identity) and also declare a natural key (on the columns that the user sees and uses) too. I NEVER willingly let the user even know that I have surrogate keys, much less show them the values of those keys because what users can see they will eventually want to change. This allows my applications to use the surrogate key freely, but the alternate key keeps the data "clean" for the users too.
    that's a great paragraph, as it touches on so many aspects of the issue

    perhaps in a client/server app, you can offer a dropdown list using some sort of code as the value that the user selects, right? this would be your surrogate key, yes? and of course client/server users don't see the underlying code...

    well, in teh interwebs, those dropdowns use html, which the user can see, so you cannot really escape having them see some key value

    on the gripping hand, the user isn't going to want to change a surrogate key (especially one as obscure as a guid)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    games ( game, gamedate, location, hteam, vteam, hscore, vscore )
    teams ( team, name )

    i want a query to show the best for/against score differential as home team

    piece of cake to understand, pretty hard to explain to the database engine without specifying which of the columns you want to join on

    note that hteam and vteam are each a FK to teams
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2002
    Posts
    189
    I'm sorry. I'm being difficult. I know you're (both!) right, because if you weren't, it would be me sitting trying patiently to explain it to you. But ignorance has never stopped me refusing to concede a point

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •