Re: Can I use an n:n relationship here?

Hi,

>PS. I call them "intersect tables" some call them "resolver tables",
>"gerund tables", "linking tables" etc.. they are usually used to fix
>many to many relationships.

The most modern, widely used (de facto) and clearly understandable term is "associative" for either entity (logical model) or table (physical data model).
Yes, associative tables/entities are used to resolve/change/break-down many-to-many into one-to-many relationships. The purpose of that is many-to-many relationships should be eliminated because they produce the following problems: (1) they hide meaning of the relationships, (2) the uniqueness of both parent and child rows/instances cannot be guaranteed.
(generally this fact is one of the initial requirements of model design stages before you begin to normalize you model)

>I have a table, tb_txn, which I use to hold transactions. These
>transactions may come from a file that is stored in a table, tb_file
>(tb_file is a parent record). the file may be broken down into
>sections which are stored in another table, tb_section (tb_section is
>the parent). The transactions may also come from an HTTP port, which
>has no table (no parent).

In your case, for successful design the major question is whether the fact that file (TB_FILE) may be partitioned into sections (TB_SECTION), is it current system (which undependable from the DB design) implementation?
say the file may be stored as sections on disk by Web/ECommerce server?

Roughly, the following is conceptual model for your case:

The following are things/entities of interest: * Transaction, File, File-Section, HTTP-Source

Consider entities have the following possible attrributes:

* Transaction (Unique ID, Source, Source-type, Date-time, ...)
* File (Name, Date-time, Size, ...);
* File-Section (Name, Date-time, Size, ...);
* HTTP-Source (URL, Date-time, Size, ...);
* ....

NOTE:
Name of File or File-section is ether file name only but also can be a full-path.
URL may include protocol (http,ftp,), Http port which can be separated for convinience in other design stages

Suppose the following relationships:

* Transaction may have either File or HTTP-Source as its source;
* Transaction may have 1 or more sources;
* A File may be stored as 1 or more File-Sections;
* ....

Now suppose that we want to query the following from the system: (Sorry, it's just a guess...)

* Source, Source-type of the particular Transaction;
* IF Source-type is File, all File-sections of that File.
* ....

is it right?

Alex Petrov
System analyst,mcdba,ocp

~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ From: seankraft_at_yahoo.com (Sean Kraft) Subject: Can I use an n:n relationship here? Date: 17 Jul 2002 12:00:34 -0700
Organization: http://groups.google.com/

Hi,  

I have a table, tb_txn, which I use to hold transactions. These transactions may come from a file that is stored in a table, tb_file (tb_file is a parent record). the file may be broken down into sections which are stored in another table, tb_section (tb_section is the parent). The transactions may also come from an HTTP port, which has no table (no parent).  

Simply put the relationships to a record in tb_txn are varied or nonexistant, and the client wants to add sources as they go.  

The current system holds the "file_id" which is a foreign key to tb_file. To improve on this I may suggest that they create an intersect table which holds this key and the txn_id. In the case where tb_txn is related to a section I would create another intersect table to relate tb_txn to tb_section. If there is no saved source, as with the HTTP port there would be no relationship stored. If in the future there needs to be a new source I can always add a new intersect table to relate tb_txn.  

My question: Does this violate any rules of database design? Particularly the fact that there may be cases where there is no relationship stored at all?  

Thanks,
Sean  

PS. I call them "intersect tables" some call them "resolver tables", "gerund tables", "linking tables" etc.. they are usually used to fix many to many relationships.

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG