Mark Forums Read

Using a seperate table to 'join' make a table relation

Go Back

comp.databases.theory comp.databases.theory

Reply

Page 5 of 5

5

#41

Old

Tegiri Nenashi

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 08:42 PM

On Jan 10, 5:46*pm, JOG <j... (AT) cs (DOT) nott.ac.uk> wrote:

Quote:

junction table - instersection table - associative entity - all
different names for a setup where a relation contains two attributes
which are foreign keys, referencing tuples in other tables. And very
useful stuff in database design they are too, so if you discovered
them yourself, much kudos. As with everything, we're never the first
to think of things, but its a good sign hey

My perspective is that tables frequently arrange into a lattice
structure. Consider

table Orders (
ord# integer,
...
)

table Items (
ord# integer,
item#,
...
)

Clearly, these two are in one to many. Pretty straightforward, so
far... If we add Invoices

table Invoices (
ord# integer,
inv# integer,
...
)

however, things start to get more interesting. Each invoce informally
corresponds to a partially fulfilled order, so that sometimes one
order is covered with more than one invoice. Naturally there is a join
table between Invoices and Items (which we better start calling
OrderItems)

table InvoiceItems (
ord# integer,
inv# integer,
...
)

Now, the lattice interpretation:
i. Inner union of Invoices and OrderItems contains all the ord#
occuring in the Invoices or in the OrderItems. If database design were
entirely denormalized, then OrderItems and Invoices tables duplicated
all the columns from Order, and the ineer union was the Order table.
With normalized design, however, the Order table would contain more
columns. Therefore, the Orders table is larger (in the lattice order
sence) than the inner union of Invoices and OrderItems.
ii. Similarly, join of Invoices and OrderItems is a table that is
smaller than InvoiceItems.

Reply With Quote

#42

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#43

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#44

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#45

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#46

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#47

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#48

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

#49

Old

David Cressey

Posts: n/a

Re: Using a seperate table to 'join' make a table relation - 01-10-2008 , 11:28 PM

<bissatch (AT) yahoo (DOT) co.uk> wrote

Quote:

Thanks for all that, I did search for join table but got loads of SQL
JOIN command stuff but ill keep looking. I briefly touched on
normalization at uni but im going to do learn more on database design.

Anyway, good to see im somewhat on the correct track

Here's a pretty good summary of modeling and design. Unfortunately the
custodians are archiving it. It's pretty condensed, and it's not perfect.
But it might help.

http://www.utexas.edu/its-archive/wi.../overview.html

Reply With Quote

Reply

Page 5 of 5

5

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.