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

From: Alex Petrov <master.db_at_mail.ru>
Date: Fri, 19 Jul 2002 01:08:04 +0000 (UTC)
Message-ID: <b5c45e80bda4d18ed53041d06cdece9d.54296_at_mygate.mailgate.org>


Hi Sean,

>So you are saying that I should store the HTTP source (or any source)
>as well as the the file and file section? This seems to make sense,
>then I would eliminate the problem of not having a parent record for a
>transaction.

No, I was suggesting you tell me that and other details about your conceptual design.
Sure, you may store File and HTTP and whichever sources together =in the same table=, and sucj approach will simplify the design (and queries also, a little) a lot!
Of course, this depends on the attributes/columns you want to have for File/HTTP/Email sources, if their number and some of them have similar sense for File/HTTP/etc sources -- it's quite possiblem, say the File/HTTP source ("Source") table may look like that

Source (Source_Id (PK), Type, Date-time, Size, ... )

where Source_Id - is either file name/path or URL string, Type - is source specifier (can be disk file,http,email, etc)

>With this model "Transaction.Source" would hold a foreign key to
>whatever table the source is in? So you are saying I should eliminate
>the "associative" table and add a SOURCE_TYPE table that will aid in
>the query mentioned above?

Yes, associative table is not necessary and ONE Transaction may have ZERO (but it still seems strange to me that the Transaction mayn't have a source ), ONE or MORE sources.
And you should use ***not SOURCE_TYPE table*** but TRANSACTION.SOURCE_ID column (in child table TRANSACTION) as FK to refer to PK, ie. the SOURCE.SOURCE_ID column (in parent table SOURCE)

Important note: above we talked about logical model, in the physical model you may want to implement surrogate keys (say autoincrement numeric values against char strings) for query performance reasons

Good luck

Alex Petrov
Sys analyst,mcdba,ocp

~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~~ From: seankraft_at_yahoo.com (Sean Kraft) Subject: Re: Can I use an n:n relationship here? Date: 18 Jul 2002 05:56:18 -0700
Organization: http://groups.google.com/

> 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.
> * ....
So you are saying that I should store the HTTP source (or any source) as well as the the file and file section? This seems to make sense, then I would eliminate the problem of not having a parent record for a transaction.

The query above is exactly what I need, and the other queries would want to retrieve the HTTP information or whatever specific information comes in the future.

> 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, ...);
With this model "Transaction.Source" would hold a foreign key to whatever table the source is in? So you are saying I should eliminate the "associative" table and add a SOURCE_TYPE table that will aid in the query mentioned above?

This way I could add a new source by adding a new table to hold the source (ex:TB_EMAIL) and then add a row to SOURCE_TYPE, and I would be ready to go.

Thank you for providing such a well thought out answer so far.

,Sean

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Fri Jul 19 2002 - 03:08:04 CEST

Original text of this message