Solved

Enforce table relation

Posted on 2007-11-23
8
243 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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