473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Enforcing one-to-many relationship in an existing join table

BD
Hi, all.

I need to enforce a one-to-many relationship on 2 tables, with a join
table.

Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are child FKs to other tables (no
nulls will exist in these columns).

Logically, one customer can have several accounts, but one account can
only belong to one customer.

It seems to me that a straightforward way to enforce a one-to-many
relationship, where one had not existed previously (ie I'm altering
existing tables rather than starting from scratch) would be to simply
put a uniqueness constraint on acct_no. I've already checked existing
data, and there are no duplicate values in the acct_no column

Assuming no additional business rules exist, is a simple uniqueness
constraint a reasonable way to enforce such a relationship?

Cheers,

BD
Sep 12 '08 #1
6 3774
On Sep 12, 2:03*pm, BD <robert.d...@gmail.comwrote:
Hi, all.

I need to enforce a one-to-many relationship on 2 tables, with a join
table.

Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are child FKs to other tables (no
nulls will exist in these columns).

Logically, one customer can have several accounts, but one account can
only belong to one customer.

It seems to me that a straightforward way to enforce a one-to-many
relationship, where one had not existed previously (ie I'm altering
existing tables rather than starting from scratch) would be to simply
put a uniqueness constraint on acct_no. I've already checked existing
data, and there are no duplicate values in the acct_no column

Assuming no additional business rules exist, is a simple uniqueness
constraint a reasonable way to enforce such a relationship?

Cheers,

BD
I think that's the right solution--indeed, it's rather elegant.

--Jeff
Sep 13 '08 #2
This is a little hard to figure out because of all the non-relational
terminology and vagueness from lack of DDL.
>Say the join table [sic: relationship table?] contains account information [if it has account information, it should be the Accounts table]. It has cust_no and acct_no. Both cust_no and acct_no are child [sic: referencing?] FKs to other tables (no nulls will exist in these columns). <<
>It seems to me that a straightforward way to enforce a one-to-many relationship, where one had not existed previously (i.e. I'm altering existing tables rather than starting from scratch) would be to simply put a uniqueness constraint on acct_no. [the acct_no where? In the Accounts table you did not post or the relationship table you did not name?] I've already checked existing data, and there are no duplicate values in the acct_no column <<
I think this is what you meant. The AcctOwnership table will have
information about the relationship between the accounts and the owners
-- who and when it was set up, the tax status, etc.

CREATE TABLE Customers
(cust_nbr .. PRIMARY KEY,
...);

CREATE TABLE Accounts
acct_nbr .. PRIMARY KEY,
...);

CREATE TABLE AcctOwnership
(cust_nbr ..REFERENCES Customers,
acct_nbr .. UNIQUE
REFERENCES Accounts
PRIMARY KEY (cust_nbr, acct_nbr),
...);

Sep 13 '08 #3
BD
Point taken. Actually, it's more like this:

Actually, it's more like this.

CREATE TABLE Customers
(cust_nbr .. PRIMARY KEY,
...);

CREATE TABLE Accounts
(acct_nbr .. PRIMARY KEY,
...);

CREATE TABLE AcctOwnership
(SURROGATE_ID number PRIMARY KEY,
cust_nbr ..REFERENCES Customers,
acct_nbr ..REFERENCES Accounts,
...)

As to why the SURROGATE_ID is there... in reality, my issue has
absolutely nothing to do with customers and accounts - it's for a
completely different business application. I am describing it in terms
of customers and accounts for the sake of straightforward
communication.

The initial relationship table was not created to impose uniqueness on
the cust_nbr/acct_nbr values (it's not a composite primary key as is
the case in many relationship tables), but only to ensure that the
referencing values exist in the referenced tables.
We now want to ensure that an account belongs to one and only one
customer - but a customer can have one or more accounts.

So I *believe* that a unique constraint on AcctOwnership.acct_nbr will
fulfill this requirement.

Thanks!

On Sep 13, 6:48*am, --CELKO-- <jcelko...@earthlink.netwrote:
This is a little hard to figure out because of all the non-relational
terminology and vagueness from lack of DDL.
Sep 15 '08 #4
>CREATE TABLE AcctOwnership
(SURROGATE_ID number PRIMARY KEY,
cust_nbr ..REFERENCES Customers,
acct_nbr ..REFERENCES Accounts,
..); <<

Get rid of the "surrogate_id" at once. A quote from Dr. Codd:
"..Database users may cause the system to generate or delete a
surrogate, but they have no control over its value, nor is its value
ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and
Codd, E. (1979), Extending the database relational model to capture
more meaning. ACM Transactions on Database Systems, 4(4). pp.
397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means
never used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two
companies merge, the two employee databases might be combined with the
result that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it
has ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates.

[emphasis begin] Database users may cause the system to generate or
delete a surrogate, but they have no control over its value, nor is
its value ever displayed to them....." (Codd in ACM TODS, pp 409-410)
[emphasis end].

That means if a magical created numbering (auto-increment, IDENTITY,
etc.) were a surrogate, we would not see its values, print them out as
invoice numbers, search on them, and so forth. That means if "magic
number" were a surrogate, we could drop it and the schema would still
work. Like an index. But "magic number" fails on both those
points.
>As to why the SURROGATE_ID is there... in reality, my issue has absolutely nothing to do with customers and accounts - it's for a completely different business application. I am describing it in terms of customers and accounts for the sake of straightforward communication. <<
It is very hard to answer a question where you have been told less
than nothing -- vague data is better than false data.
>The initial relationship table was not created to impose uniqueness on the cust_nbr/acct_nbr values (it's not a composite primary key as is the case in many relationship tables), but only to ensure that the referencing values exist in the referenced tables. <<
Unh? The referencing values MUST exist in the referenced table,
otherwise, they cannot be referenced!!! ARRRGHH!
>We now want to ensure that an account belongs to one and only one customer - but a customer can have one or more accounts. <<
I do a cute example in my books with wives and husbands in two tables,
which then are referenced by a monogamy table (UNIQUE wife, UNIQUE
husband), a polygamy table (UNIQUE wife, husband), polyandry table
(wife, UNIQUE husband) and an Orgy table (wife, husband). But it is
important not to have a magical key that will allow redundant
duplicate data.
Sep 15 '08 #5
BD
It is very hard to answer a question where you have been told less
than nothing -- *vague data is better than false data.
And the DDL I posted was worthless as well, I presume? Seems to me it
illustrated the relationships between the tables appropriately.

Thanks for the rant about the surrogates. I'm sure it's very useful
information, but it doesn't help me one whit - partly because that
wasn't what I was asking about, and partly because the environment I'm
working in made decisions like that many months ago, and it's not
going to change now.

Nice sarcasm, by the way. Feel better?
Sep 15 '08 #6
>.. and partly because the environment I'm working in made decisions like that many months ago, and it's not going to change now. <<

Then don't expect to have data integrity. I have been at this for
three decades and Dr. Codd was right. Those "magical pseudo-surrogate
non-keys" will come back and bite you.

I am the guy who gets called in to try to fix things when they start
to fall apart. I have a list of symptoms I built over those decades.
I have started calling the "magical pseudo-surrogate non-keys" by the
more descriptive name "Kabbhala numbers". because that is more like
what they are.

The Jewish mystical system of numerology believers that God put a
number on everything in his creation (in Hebrew digits and letters are
the same symbols, hence the number of the beast and all that jazz).
If you know the 213 digit number that is the True Name of God, you can
get all sorts of power. But the idea is that a magical number can be
assigned to anything, without regard to any context .
>Nice sarcasm, by the way. Feel better? <<
Quoting Dr. Codd is sarcasm? I would call it an appeal to authority
and definitions.

Sep 15 '08 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: rashika | last post by:
Hi : Can i call 2 procs within one task? I have sp_proc1 ? (and have declared one global variable as input parameter) now i have another sp_proc2 which uses same input parameter but if i...
7
by: mp | last post by:
No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information...
2
by: Edward | last post by:
I've one datagrid and one frame with id ="frmdetail", in the datagrid, there is one template column which includes one HyperLink, <asp:HyperLink id="detaillnk" runat="server" text='detail'...
1
by: teddysnips | last post by:
Crystal Report problem - HELP! The fragments below come from two fundamentally identical applications. The one at the top - PrintEASAReport - throws an exception on the final line with the...
5
by: Peter Rilling | last post by:
Okay, the other day I was talking with someone about assemblies. He said something that I am not really sure about. He said that a DLL or EXE can contain more then one assembly (although the IDE...
10
by: ddtbhai | last post by:
Hello folks, Just wanted to know if there are some 'standard' approaches to enforcing an order in the invocation of calling functions. It is usually needed when initializing some object. e.g...
20
by: Chris | last post by:
I'm not sure if this has been done before, but I couldn't easily find any prior work on Google, so here I present a simple decorator for documenting and verifying the type of function arguments....
41
by: Chris Lasher | last post by:
A friend of mine with a programming background in Java and Perl places each class in its own separate file in . I informed him that keeping all related classes together in a single file is more in...
7
by: montyphyton | last post by:
Some recent posts about Python programming style got me thinking. Since we have the PEP 8 which gives some guidelines about the style to be used, do we have any program that can check for...
7
by: somnamblst | last post by:
I am using jQuery & hideAllExcept.js from this demo tute http://enure.net/dev/hide-all-except-one/ The issue is that on a page with other content, the images I have placed in the toggleThis...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.