Re: Re: Can I use an n:n relationship here?
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.ORGReceived on Fri Jul 19 2002 - 03:08:04 CEST