tutorial about sql diagram tool?

Question

tutorial about sql diagram tool?

neotokyo

SSCertifiable

Points: 7239
More actions
November 30, 2005 at 3:20 pm

#115623

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?

_________________________

Viewing 14 posts - 1 through 13 (of 13 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

neotokyo SSCertifiable Points: 7239 More actions · Answer 2

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.

_________________________

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

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?

_________________________

neotokyo SSCertifiable Points: 7239 More actions · Answer 4

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.)

_________________________

Rick Ziminski Old Hand Points: 323 More actions · Answer 5

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:

Inclusion of all SQL 2000 and SQL 2005 objects
Interactive display
Proprietary dependency engine, independent of Microsoft sysdepends table

http://www.red-gate.com/products/sql_dependency_viewer/

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

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.

neotokyo SSCertifiable Points: 7239 More actions · Answer 7

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?

_________________________

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

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

neotokyo SSCertifiable Points: 7239 More actions · Answer 9

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!

_____________________

_________________________

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 11

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.

neotokyo SSCertifiable Points: 7239 More actions · Answer 12

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?

_________________________

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 13

"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.