DataSet Key Problems

  • Thread starter Thread starter Paul Benati
  • Start date Start date
P

Paul Benati

We have three tables:

* Companies
* Addresses
* CompanyAddresses

CompanyAddresses contains a CompanyId, AddressId, and Role. Role defines the
type of address (such as Billing or Shipping). I am trying to setup a
relationship in the XML Schema Designer in VS.NET so that rows from the
Addresses table can be retrieved based on the AddressId in the
CompanyAddresses table. The AddressId column in the CompanyAddresses table
is *intentionally* not constrained to be unique.

Here is the issue:

When we try to setup a CompanyAddresses.AddressId to Addresses.AddressId
relationship, the designer constrains the CompanyAddresses.AddressId column
to be unique (which we do not want it to be). We have taken an alternative
approach by creating a composite key on the CompanyAddresses table then
linking only the CompanyAddresses.AddressId to the Addresses table, however
the designer will not allow this.

So my question is; how do I define a relationship in which the column in the
master table is not unique?

Example Data:

A row from the Companies Table:

CompanyId | Name
------------------------
1 | Test Company


Addresses Table:

AddressId | AddressText
-----------------------
1 | 1215 Hightower Trail...
2 | 2546 Stonington RD...

CompanyAddresses Table:

CompanyId | AddressId | Role
----------------------------
1 | 1 | Primary
1 | 1 | Billing
1 | 2 | California Division

Hope you can help!

Thanks -

--

Paul Benati, MCP ASP.NET
VP Development
Advanced Productivity Software, Inc.
(770) 998 - 9698 x101
 
Hello Paul

Rethink your Many to Many design. CompanyID and AddressID appear to make
the unique identity of a business address. I am assuming that the Role is
not included as part of the CompanyAddress entity's key structure. With
this being so, you can only have one row with the same combo of CompanyID
and AddressID.

If you want to allow for the same address representing the same company in
different roles, then include the Role as part of the key. My suggestion is
to move the role description into a lookup table and use an auto increment
key on that table as well. thus:

CompanyId | AddressId | Role
----------------------------
1 | 1 | 1
1 | 1 | 2
1 | 2 | 3

Been down this road before :-)

Ibrahim Malluf
 
Ibrahim,

Thanks for your reply, but this is not the problem I am trying to solve.
This issue is: How do I define a relationship (in the XML Designer of
VS.NET) in which the column in the master table is not unique?

BTW, I simplified the description of problem. In fact Role is actually
RoleId and references a table named AddressRoles. Moreover, the primary key
on the CompanyAddresses table is indeed defined by all three columns (i.e.,
CompanyId, AddressId, and RoleId).

Best regards,

Paul

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++
 
IbrahimMalluf said:
Hello Paul

Rethink your Many to Many design. CompanyID and AddressID appear to make
the unique identity of a business address. I am assuming that the Role is
not included as part of the CompanyAddress entity's key structure.
Click to expand...

Actually it is; a single address may have multiple roles. In fact, the
sample data posted shows this.
 
Back
Top