Re: DB Design question: Do you create a separate PK in the join table?

I agree with pete. It is not mandatory to have a primary key in the objects you are replicating. But while configuring - one needs to specify uniqueness ( for future - conflict resolution and data integrity) thre by you could user set column ( use both FK's ).

HTH! Ramana
rchamart_at_temple.edu

 Thu, 13 Jan 2000 05:43:56 -0800 Pete Sharman (psharman_at_us.oracle.com) wrote:
: This is a multi-part message in MIME format.
: --------------1FFA9AA0580883C581EC2237
: Content-Type: text/plain; charset=us-ascii
: Content-Transfer-Encoding: 7bit
:
: This is in fact incorrect. Replication DOES work on tables without primary
: keys, you just need to tell Oracle what would be the columns to treat as
: though they were primary keys. From the 8i replication API manual:
:
: SET_COLUMNS procedure
:
: To use an alternate column or group of columns, instead of the primary key, to
: determine which columns of a table to compare
: when using row-level replication. You must call this procedure from the master
: definition site.
:
: See "Using Multimaster Replication" in the Oracle8i Replication manual
:
: Syntax
:
: DBMS_REPCAT.SET_COLUMNS (
: sname IN VARCHAR2,
: oname IN VARCHAR2,
: { column_list IN VARCHAR2
: | column_table IN DBMS_UTILITY.NAME_ARRAY } );
:
:
: Note:
:
: This procedure is overloaded. The column_list and column_table
: parameters are mutually
: exclusive.
:
: HTH.
:
: Pete
:
: Andreas Michler wrote:
:
: > Yes your are right.
: > Every table in every database should have 1 primary key.
: > I.e. an replication under oracle does not work correcty on tables without
: > pks.
: >
: > gmei_at_my-deja.com wrote:
: >
: > > Hi:
: > >
: > > We are designing a new oracle database for shop. We just had a long,
: > > heated debate about whether we should create an independent Primary key
: > > in join tables. Fox example:
: > > We have three tables: UserTable, AccountTable, UserAccountTable. And we
: > > want to support that a user can have several account and an account can
: > > be owned by several users (join account).
: > >
: > > UserTable:
: > > User_ID (PK)
: > > User_FirstName
: > > User_LastName
: > > ....
: > >
: > > AccountTable:
: > > Account_ID (PK)
: > > Account_Name
: > > ...
: > >
: > > As far as UserAccountTable goes, there are two options:
: > >
: > > 1. UserAccountTable:
: > > User_ID (FK)
: > > Account_ID (FK)
: > >
: > > The primary key in this table is the composite key which is made of
: > > both User_ID and Account_ID.
: > >
: > > 2. UserAccountTable:
: > > UserAccountID (PK)
: > > User_ID (FK)
: > > Account_ID (FK)
: > >
: > > Here you still have FK constraints, but you add one independent PK.
: > >
: > > I am for option 2. I think every table should have it's own independent
: > > PK. The systems I designed and worked before always do that. It just
: > > makes the DB admin work easier later (in case you might want to do
: > > batch updates in the join table). But the frond end programmers are
: > > against this, saying it creates extra complexity, and it will confuse
: > > them when they do java code.
: > >
: > > I am a very open minded person and I am asking you, the experience db
: > > guru, to give your opinion of what is the "better" option. What are
: > > the potential problems later in each approach?
: > >
: > > The thing I am afraid of is that we made the decision now and 6 months
: > > later we may need to change it. That will cause lots of problem because
: > > there are too many places to change. I would rather to make
: > > the "better" (or "correct") decision now and live with that.
: > >
: > > Thanks for your time and you help is appreciated.
: > >
: > > If you reply, please send a copy to zlmei_at_hotmail.com
: > >
: > > Guang
: > >
: > > Sent via Deja.com http://www.deja.com/
: > > Before you buy.
: >
: > --
: > -------------------------------------
: > ADICOM Informatik GmbH
: > Andreas Michler
: > Wiesfleckenstr. 34
: > 72336 Balingen
: > Tel: 07433/9977-57,Fax: -90
: > E-Mail: Andreas.Michler_at_adicom.de
: > http:\\www.adicom.de
: > -------------------------------------
:
: --------------1FFA9AA0580883C581EC2237
: Content-Type: text/x-vcard; charset=us-ascii;
: name="psharman.vcf"
: Content-Transfer-Encoding: 7bit
: Content-Description: Card for Pete Sharman
: Content-Disposition: attachment;
: filename="psharman.vcf"
:
: begin:vcard
: n:Sharman;Pete
: tel;cell:+1.650.868.9969
: tel;fax:+1.650.633.1506
: tel;work:+1.650.607.0109
: x-mozilla-html:FALSE
: org:Advanced Technology Solutions;Oracle Corporation
: adr:;;500 Oracle Parkway M/S OPL-A4019;Redwood Shores;CA;94403;USA
: version:2.1
: email;internet:psharman_at_us.oracle.com
: title:Managing Principal Consultant
: note;quoted-printable:**** The statements and opinions expressed here are my **** <br>=0D=0A **** own and do not necessarily represent those of **** <br>=0D=0A **** Oracle Corporation. **** <br>=0D=0A <br>=0D=0A "Controlling application developers
is like herding cats." <br>=0D=0A Kevin Loney, ORACLE DBA Handbook <br>=0D=0A "Oh no it's not! It's much harder than that!" <br>=0D=0A Bruce Pihlamae, long term ORACLE DBA <br>
: x-mozilla-cpt:;9088
: fn:Pete Sharman
: end:vcard
:
: --------------1FFA9AA0580883C581EC2237--
:
Received on Thu Jan 13 2000 - 11:58:30 CST