Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Enforce table relation

Posted on 2007-11-23
8
Medium Priority
?
253 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:
Munawar Hussain earned 400 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 200 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

916 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