November 30, 2005 at 3:20 pm
i'm trying to find information about the diagraming tool for sql so that i
can build proper relationships with it.
one of the questions i have right now is... how do i know when to make one-to-many
relationships and many-to-many relationships?
thoughts?
_________________________
November 30, 2005 at 3:47 pm
This is going to depend on the relationship between the two "entities" you're modeling. For instance:
If I have cars and tires as my entities, I'll have a one-to-many relationship from cars to tires (a tire can only be on one car but a car should have 4 tires).
If I have cars and colors as my entities, I'll have a many-to-many. After all, a car can have more than one color and colors can be on multiple cars. That's a many-to-many relationship.
K. Brian Kelley
@kbriankelley
November 30, 2005 at 3:59 pm
thanks for the reply. i'm greatful!
ok... great. that makes good sense to me, but...
now that i have a clear picture of the relationship,
how would i go about setting that up say... from
the sql-diagram tool?
i know alittle about it already. for example; the
relationship properties are accessible either from
the menu, or double-clicking on the link between
tables. furthermore; i know a key represents a
primary, and when dragging columns around there is
infinity icon at the other end.
my question is... if you have a car table, and a tire table, and then drag a (pk)column from the car table to the tire table... would you infact then be producing a
'many-to-many' relationship or a single foreign key
relationship?
or.. does this require dragging a column from each
table back to one another? like 2 links instead of one.
see what i mean? i'm confused on that dragging column business. 1 link, or 2 links... which 'defines'
the relationship for query purposes?
if i wanted to see how many tires for each car for example; how would sql know to relate those?
don't know if that makes any sense, but just trying to
get a grasp of it.
_________________________
November 30, 2005 at 4:25 pm
is what i'm describing more like simple 'referential integrity'?
if i have a car table, and tires table, and want to run a select
statement against them to find out how many tires a car has,
then would this be considered a 'referential' question, or a
relationship question?
_________________________
November 30, 2005 at 8:38 pm
i got it now.
Each entry in the drop-down list is preceded by an icon. A key icon indicates that the table participates in the relationship as the referred-to table. An infinity icon indicates that the table participates as the referring table. (The referring table has the relationship's foreign-key constraint.)
_________________________
December 1, 2005 at 1:45 pm
If you're looking for a GUI tool, try
SQL Dependency Viewer analyzes Microsoft SQL 2000 and SQL 2005 databases and produces an interactive dependency diagram.
Its features include:
http://www.red-gate.com/products/sql_dependency_viewer/
December 2, 2005 at 10:04 am
The links in the SQL Server diagram tool are always one-to-many. The links are all foreign keys, and the field(s) referred to by a FOREIGN KEY constraint must have a UNIQUE or PRIMARY KEY constraint. It could be a one-to-one relationship if both sides have unique constraints, but the foreign key itself only enforces one-to-many.
If there is a many-to-many relationship between two tables that you wish to enforce with foreign keys, you need an intermediate table with records containing the keys to the two tables.
A many-to-many relationship can be shown in an Erwin or Visio diagram (or some other tool) that isn't attached to the database. The diagrams in SQL Server are a direct reflection of database structure, so a many-to-many relationship can only be shown as going through a cross-reference table.
December 2, 2005 at 11:17 am
so it's not possible to set up a many-to-many relation ship with the diagram
tool? i've been trying it, and it's prompting me to create rows so that the
table can be validated. without them i can't create it in the diagram.
once it's done say for example through query analyzer... you can then see it
with diagram right? just can't create it there.
furthermore; how the heck can you create the intermediate table with qa? you
would have to populate the table some how with the data from the 2 other tables
right?
thoughts?
_________________________
December 2, 2005 at 12:06 pm
Many-to-many relationships are modeled with what is typically called a join table or a junction table (but that's just two of many names used). You'll find more about how relationships get modeled in this KB article. While it is for Access, the same concepts apply in SQL Server:
Defining relationships between tables in a Microsoft Access database (304466)
K. Brian Kelley
@kbriankelley
December 2, 2005 at 12:27 pm
many thanks.
i'm going over the example they have there.
i just need more practice at making these things.
even more practice with joins. i have a tough time
seeing the logic in my head for populating the junction
table with data from the other 2 tables.
thanks for all the help!
_____________________
_________________________
December 2, 2005 at 12:37 pm
If you want a couple of examples to wrap your head around:
1) Entities: subscriber, magazine. join table: subscriber to magazine.
2) Entities: programmers, programming languages. join table: programmer to programming language.
K. Brian Kelley
@kbriankelley
December 2, 2005 at 12:54 pm
The diagrams in SQL Server directly reflect database structures, it's not simply a tool for making pretty pictures. The links in the diagram ARE foreign keys in the database. If you add or remove links in the diagram, you add or remove foreign keys in the database.
If you have your Cars table with primary key pkCars, and your Colors table with primary key pkColors, you can create a many-to-many relationship through a CarColors table:
create table Cars (
pkCars int identity not null primary key clustered,
Description varchar(100))
create table Colors (
pkColors smallint identity not null primary key clustered,
ColorName varchar(100))
go
create table CarColors (
pkCars int not null references Cars (pkCars),
pkColors smallint not null references Colors (pkColors))
Add these three tables to a diagram, and it will show the links.
December 2, 2005 at 1:04 pm
you guys rock!
these example are just what i needed. thanks guys!
another question thought...
so if i create the 3 tables Cars, Colors, CarColors, then open
the diagram 'add the 3 tables' the links should appear automatically?
this will only happen if i allow to 'add' linked tables automatically right?
_________________________
December 2, 2005 at 1:21 pm
"Add linked tables" will add tables (not currently in the diagram) that are linked to diagram tables. It doesn't control whether the links appear or not.
If two tables in the diagram have a foreign key relationship, the link will appear. You can't turn it off.
If you delete a link in the diagram, the foreign key constraint is dropped from the database.
You could create a diagram and add only one of the tables, then use "Add linked tables" to pull the other tables in.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply