July 2, 2003 at 5:24 am
I have 2 tables - 1 for People [PersonDetails] and one for Organisations [CRMTestOrg]. I also have a table for Addresses [CRMAddress] which I want to use to store address information for both People and Organisations. I have used a relationship based on both the ID and the name of the table. However I am unable to enter any details because it will always fail one relationship. Is there a way of doing this so SQL will check both relationships and if one works then it will allow entry/deletiion. I have included a simplified script for the tables and relationships if someone has the time/inclination to have a look. The problem arises as soon as any address info is entered.
SCRIPT:
CREATE TABLE [dbo].[crmTestORg] (
[orid] [int] IDENTITY (1, 1) NOT NULL ,
[orName] [char] (10) NOT NULL ,
[ownerTable] [char] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CRMAddress] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[adName] [char] (20) NOT NULL ,
[adOwnerTable] [char] (10) NOT NULL ,
[adOwnerID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PersonDetails] (
[prPersonID] [int] IDENTITY (1, 1) NOT NULL ,
[prName] [char] (10) NOT NULL ,
[prOwnerTable] [char] (10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[crmTestORg] WITH NOCHECK ADD
CONSTRAINT [DF_crmTestORg_owner] DEFAULT ('Org') FOR [ownerTable],
CONSTRAINT [PK_crmTestORg] PRIMARY KEY NONCLUSTERED
(
[orid],
[ownerTable]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CRMAddress] WITH NOCHECK ADD
CONSTRAINT [PK_CRMAddress] PRIMARY KEY NONCLUSTERED
(
[AdID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PersonDetails] WITH NOCHECK ADD
CONSTRAINT [DF_PersonDetails_prOwnerTable] DEFAULT ('Person') FOR [prOwnerTable],
CONSTRAINT [PK_PersonDetails] PRIMARY KEY NONCLUSTERED
(
[prPersonID],
[prOwnerTable]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_CRMAddressPerson] ON [dbo].[CRMAddress]([adOwnerTable], [adOwnerID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CRMAddress] ADD
CONSTRAINT [FK_CRMAddress_crmTestORg] FOREIGN KEY
(
[adOwnerID],
[adOwnerTable]
) REFERENCES [dbo].[crmTestORg] (
[orid],
[ownerTable]
),
CONSTRAINT [FK_CRMAddress_PersonDetails] FOREIGN KEY
(
[adOwnerID],
[adOwnerTable]
) REFERENCES [dbo].[PersonDetails] (
[prPersonID],
[prOwnerTable]
)
GO
July 2, 2003 at 6:16 am
What you are attempting to do is not possible or logical. To work, each address stored will have to have an adOwnerID & adOwnerTable matching in both crmTestORg and PersonDetails and result in mismatches unless the id's are absolutely unique in the database.
Because you are trying to put the FK on addresses, this indicates that crmTestORg and PersonDetails can exists without an address.
Two possiblities I can think of
1. Remove adOwnerTable & adOwnerID from CRMAddress and add AdID to crmTestORg and PersonDetails and make FKs to CRMAddress. If you allow AdID to be null then you can store crmTestORg and PersonDetails without an address, otherwise create a dummy CRMAddress with an AdID = 0 and blank details. This method means you have to create CRMAddress before storing crmTestORg and PersonDetails.
2. Remove adOwnerTable & adOwnerID from CRMAddress. Create two new tables as links between crmTestORg/CRMAddress and PersonDetails/CRMAddress and put FK on each.
The best solution will depend on what data integrity you wish to maintain, which order data is created and what is optional and what is not.
Hope this helps.
Far away is close at hand in the images of elsewhere.
Anon.
July 2, 2003 at 7:35 am
Agree with David. You cannot do this using just the foreign key relationships. The table layout with the TestOrg and Person tables having a FK relationship to the Address table is in my opinion the most logical solution.
You can still store the data in the Address table to link the address back to the 'owner' of that address, but that will only give redundant data and more troubles to keep everything up to date.
An alternative solution could be to use triggers to check the constraint, whenever an action is executed on the tables.
July 3, 2003 at 9:07 am
The comments & advice from David & Noel sure seems to be spot-on - you don't want to be causing yourself problems trying to do the impossible.
This really does sound like a bit of a conceptual and/or modelling issue. There are many cases where persons & organizations can (obviously) have the exact same address (think eponymous companies, etc.) and many people can also have the same address. While this may or may not be the case for your application, it is something to recognize - it can have functional and maintenance implications. As just one example, if you have bulk address changes (as in, the U.S.Post Office decrees it for 911 support) then you'd typically want to make each address change just once. We went through that locally a couple of years back and it was really instructive to see which local companies got it right in their billing and other mail systems.
You know the application you're building - the volumes and workflow and relationships and stuff like that. Will address information always be available for every person and organization at INSERT time or is that data (and relationship) potentially added at a later time? The comments on the default address (AdID=0) may be your simplest solution.
July 9, 2003 at 2:53 am
Thanks for your advice. The problem in having a foreign key on either Person or Organisation is that both can have more than one address. The basic thinking behind all this is that we have details of people and details of companies. Someone who has directorships of multiple companies may wish his correspondance in a particular role sent to his home address or other work address. As we will know about peoples roles it seemed to make sense to not input details of his correspondance address for each role when that information may already have been entered. Hence the idea of a single address table. I had an idea of a table [EntityType] which holds an ID and a type (Person or Organisation). I can then relate both the Person and Organisation tables to this table and each ID will be unique. The address table is also related to EntityType via ID and will allow multiple addresses. I do feel I am taking a sledgehammer to crack a nut somewhat.
July 9, 2003 at 3:31 am
I think your solution boils down to basic design and the use of one-to-one, one-to-many or many-to-many relationships.
If a person or a company has only one address then the relationships are one-to-one. If they both have only one address but the same address can be the same for more than one person or company then the relationship is one-to-many (one address to many person/company).
And just to add a little confusion here you could duplicate the address and maintain a one-to-one relationship.
But back to the subject, the one-to-many would be implemented by storing the AdressID on the person/company record.
Further to this if a person/company can have many addresses and an address can have many person/company then this is a many-to-many relationship and is maintained by using one or more link tables (or whatever the correct name is) to join person/company to addresses.
So the choice of how to design and implement the solution depends on the data itself and the processes that use it.
As for FK's, they are only used to force integrity and it is again a matter of choice. Most of my db's don't have FK's but the integrity is handled by the app but this is changing as I document my databases with Visio.
Sorry if this is a bit basic and noddy and might state the obvious but I thought it better that way. I find people sometimes get blinded by where they are and where they are going and forget where they came from. A little re-evaluation sometimes helps.
Far away is close at hand in the images of elsewhere.
Anon.
July 9, 2003 at 8:05 am
I should perhaps have explained that I have a table which links Roles (People in postions in companies) to addresses. This table should allow a particular location (room 101)to be linked to both a postion in a company and an address of that company or that person.
Hope this makes sense.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply