Solved

Enforce table relation

Posted on 2007-11-23
8
247 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:
Munawar Hussain 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 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