Solved

Enforce table relation

Posted on 2007-11-23
8
245 Views
Last Modified: 2012-05-05
I have an "address" table in a DB that is related to several other tabels. at present these relations are not enforced. I would like to enforce the relationships but I am getting an error because there is no record with ID of "0" in the parent table. How might I resolv this issue. Do I need to create tabels and seporate the records?

CREATE TABLE [dbo].[Address](
      [addressId] [bigint] IDENTITY(1,1) NOT NULL,
      [address1] [varchar](50) NULL,
      [merchantId] [varchar](20) NULL,
      [personId] [bigint] NULL,
      [clubId] [bigint] NULL,
      [labelId] [bigint] NULL,
      [studioId] [bigint] NULL,
      [userId] [nvarchar](50) NULL,
      [orderId] [bigint] NULL)
0
Comment
Question by:ruffone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Accepted Solution

by:
needo_jee earned 200 total points
ID: 20342044
you are trying to enfore parent child relation between two tables where child tables seems having an orphan record and that might be with id =0 but there is no record in parent with that id.

would be better to generate new script for complete database
create a new database
apply relationships
and move data from origional database to new one.
*********************
otherwise you need to make data valid for relationships you want to enforce
0
 
LVL 15

Assisted Solution

by:dosth
dosth earned 200 total points
ID: 20342873
Yes, JEE is correct

Check all records in child tables and see there is a record in parent table, so there is a record in child table with a parent id and there is not records in parent table, you cant do this
0
 
LVL 4

Author Comment

by:ruffone
ID: 20343528
I know what the problem is. I am trying to figure out the best solution. This is what the records look like. Below I am showing records in the "Address" table for the "Merchant", "people" and "Clubs" tables and this is typical. Is the solution as simple as just adding a "0" record to the parent table or do I have to make a "Merchant_Address", "People_Address", "Club_Address" table and separate the records for a solid long-term solution.

[addressId] [merchantId] [personId] [clubId]
1      0                 0                     1
2      1                 0                     0
3      0                 1               0      
4      0                 0               0

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20346195
<<do I have to make a "Merchant_Address", "People_Address", "Club_Address" table and separate the records for a solid long-term solution.>>
Normalizing your schema is the only long term solution...each of your columns looks like itt should be a table...
0
 
LVL 4

Author Comment

by:ruffone
ID: 20353267
isn't that a lot of tabels having the same address information?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20353300
<<isn't that a lot of tabels having the same address information?>>
What is a *lot of tables* ? If  2 , 3 extra tables created *once* can prevent all the problems of one denormalized set bag I say it's not much...

Hope this helps...
0
 
LVL 4

Author Comment

by:ruffone
ID: 20353671
I am not complaining about the amount of tables as I am the replication of data. I would instead of having one table with addresses I would have 6 tables with addresses. Is that what you are sugesting?
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 20354082
<Is that what you are sugesting?>>
What I am suggesting is that the drawbacks you will draw from having a denormalized schema (with less tables) *far* outweigths any advantage you may draw from that choice (such as updating your replication subs --> You do it *once* by clicking on some extra tables that is not what I call unbearable)...

It is very difficult to give online advice about design not knowing the full spectrum of business requirements.  I don't know for instance if a person can have 1 or many adresses or if it may have no adresses at all ?  Assuming that is the case then you would look at something like....

--In the case it may have 1 or many addresses but not 0 the structure may look like
Adress: Address
Person: FirstName, Address--(I suppose here that a Person always has least ONE address)
Merchant: MerchantName, Address --(I suppose here that a Merchant always has least ONE address)
Club: ClubName, Address--(I suppose here that a Club always has least ONE address)

--In the case a person may or may not have an address then you have to decompose the Person table into Person_WithAdresses Table and enforce the relationship with Person...Which would come back to something like...

Adress: Address
Person: FirstName, LastName --(I suppose here that a Person may have 0 address)
Person_WithAdresses: FirstName, LastName, Address(FK to Adress)
Merchant: MerchantName, Address--(I suppose here that a Merchant always has least ONE address)
Club: ClubName, Address--(I suppose here that a Club always has least ONE address)

One direct advantage of that approach is that you claim back all the space of the *0* you had in your previous table...

That can give you an idea on what to do to normalize your schema...Not convinced, suppose you want to count how many merchants and people have the same address...Such request on the other hand is very simple to do using the schema I am suggesting...

select count(*) from Person_WithAdresses inner join Merchant on Person_WithAdresses.Address = Merchant.Address...

As you can see, it is fairly easy...Now, try to get the same info using your old structure and you will comprehend what a long term solution is...

Hope this helps...
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question