Database Modeling - Relationships

Question

Database Modeling - Relationships

ukashishgarg

SSC Veteran

Points: 209
More actions
August 31, 2020 at 12:00 am

#3779961

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

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

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

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 1

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.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ukashishgarg SSC Veteran Points: 209 More actions · Answer 3

ukashishgarg

SSC Veteran

Points: 209

August 31, 2020 at 9:45 pm

#3785019

Thanks for the great input George & Scott.

hmbacon SSC Journeyman Points: 79 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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.

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

hmbacon SSC Journeyman Points: 79 More actions · Answer 6

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

Marcus

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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.

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

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.

GeorgeCopeland SSCertifiable Points: 6997 More actions · Answer 10

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

hmbacon SSC Journeyman Points: 79 More actions · Answer 11

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.