Database Modeling - Relationships

  • Comments posted to this topic are about the item Database Modeling - Relationships

  • Nice article. I refer to bridge tables as cross reference tables, and I like to name them <Table1><Table2>_XREF which makes their purpose as bridge tables clear. Like you, I name tables after the objects they describe, but I would never end their names with Tbl, which could not be more obvious.

  • Congrats, very nice article.  Informative and to the point.  And it's so great to see an intersection ("bridge") table without an identity column, instead keyed by the parent's keys, the correct way to do it.

    One minor point, though: you would never store "age" as a column, since you'd never know when it became inaccurate (except perhaps, I guess, if you need a static, point-in-time table for some reason).

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks for the great input George & Scott.

  • Thanks for a excellent article explaining relationship types and how they are implemented in a databases using foreign and primary key constraints on tables.  I am old school and have been a database designer for a many years.  The line between conceptual modeling and relational modeling and physical implementation are often blurred.  What I mean is, a conceptual model, often referred to as an entity relationship diagram or ERD, has entities and relationships.  Entities are things of interest such as "people, places or things".  They can be related by any of the three relationship types.   Tables do not have "data modeling relationships", although they are related via foreign key constraints.  I do not know of a database that can implement a 1:1 or m:m relationship between tables.  As you demonstrated, a 1:1 relationship is implemented with a foreign key constraint and a unique constraint on the child foreign key column.   And a m:m relationship between two entities is implemented as three tables and two foreign key constraints.   By the way, I have seen the common table called many things (cross reference, association, bridge)  but I like the idea of having a suffix or prefix plus the parent table names to indicate that they are implementing a many to many relationship between those two tables.

    I have joined projects with existing databases and there are certain tables that are hard to modify without breaking the code.  Usually these tables have implemented two different entities in one table which causes the problem.  This can be prevented by analyzing all requirements to identify every individual entity and its relationships to other entities.  I then identify the natural key and implement each entity and their relationships as tables and constraints following the standard practices as outlined in this article.  I agree, if the bridge table has no children there is no need for a surrogate key.  If it has children a surrogate key needs to be added.  I am not wanting to start a flame war over surrogate keys, but it is my practice to use both natural and surrogate keys.  Natural keys prevent duplicates.  Surrogate keys are used as foreign key columns since an integer ID is usually more efficient in performance and storage space is reduced if the parent table's natural key is a compound key.

    Marcus

  • I agree.  Very nice article and I share Scott's joy in seeing someone make a Bridge/Intersection/Cross Reference/M2M/Cartesian/Relational Multiplication table (heh.. those are just some of the names I've heard for it but prefer Bridge or Intersection table) with no bloody 3rd "identity" column,

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I posted a reply and it was removed as spam.  Please explain how my opinion was spam.

    Marcus

  • Hi Marcus, you are right, I do not see your post. I read it when you posted it and I really liked it. I have never seen this happen before. I have never asked for help on this website. You might poke around in Contact Us and see if you can raise an administrator. Anyway, I hope that you continue to contribute. Have a great day--George

  • hmbacon wrote:

    I posted a reply and it was removed as spam.  Please explain how my opinion was spam.

    Marcus

    Artificial "Intelligence" of the automatic SPAM removal code for this forum is the most likely culprit.  It's especially brutal for people with (IIRC) with less than 100 points especially (but not limited to) if you have any links in your post.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hmbacon wrote:

    I posted a reply and it was removed as spam.  Please explain how my opinion was spam.

    Marcus

    Released this back to normal. I don't see anything in there that would trigger filters, but perhaps the large paragraphs? Not sure since it's a ML filter.

    It's also possible someone clicked the "Report" link accidentally.

  • Hey Marcus, you are getting support from some of the top DBA consultants in the nation. That will be $1200, payable in bitcoin.

  • Thanks, George.  Put it on my account.  On account I aint gonna pay. 😉  Or open an account on the  Bank of San Seriffe and we will let Don Knuth track it.  I guess I need to get my point count up.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply