Solved

3 SQL Table Referential integrity

Posted on 2004-03-20
44
196 Views
Last Modified: 2010-05-19


I have 3 existing tables


Toy_Type    Toy_type_ID,Toy_Color

Toys_box    Toy_box_id,Toy_type_id(FK from Toy_Type table)  ==  (Box which contains toys)

Toy         Toy_Id,Toy_box_ID(FK from Toys_Box) Toy_Name,Toy_Type_id(Fk from toy_Type table)


Question:

why is Toy_type_id existing in Toy table ...We already have toy_type_id in Toys_box table right
to get the  toy type from toy table cant be join the toys_box table which has toy_type_id already defined

Isnt the toy_type_id repeating in toy table  ....
0
Comment
Question by:sainavya1215
  • 25
  • 12
  • 5
  • +2
44 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
We can't tell you that without know your data--can a "toys_box" have a different toy_type than the toy itself?

Have you looked at the data to see if a toys_box ever has a different toy_type than it's associated toy?
0
 
LVL 12

Expert Comment

by:dfiala13
Comment Utility
These are design questions that can only answered by someone who understands the business being modeled by the database, ie someone with initimate knowledge of the business and the goals of the application.  There could be a perfectly legimate reason why the FK is used in both tables, or it could be bad design.  No way to know it from this distance.
0
 

Author Comment

by:sainavya1215
Comment Utility
No Toys_box can contain a value from toy_type table only...It cant have different values... thats why my doubt was why we require toy_type_id again in toy table we any way have the box_id in the toy table ..by joining toy Table and box table we can get the type_id of toy right?????
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
it looks as if your design is missusing the relationship....

does toy_type really only  give a colour?

in which case the

toy can be red in a white box ?

just a comment...
0
 

Author Comment

by:sainavya1215
Comment Utility
Nope U get a box of toys specific to a color say red.. U can have one color in a box.... So what about my doubt now
0
 

Author Comment

by:sainavya1215
Comment Utility
Lets forget about the box color though....
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
if box can only have 1 toy type in it then no you don't need it on the box...

unless its there for some performance related reason.
0
 

Author Comment

by:sainavya1215
Comment Utility
U dint follow my question...

A box can come with Only one Type of color. U cannot have jumbled up colors in a box (That is why we have a different table Toy_type which contains toy_type_id mentioning all the colors available)

Now Box table has Toy_Type_ID in it ...Thats fine ....Thats the way it has to be

My question was there is a table which is Toy which contains Toy_id,Toy_Description , Box_ID(FK from boxtable), Toy_Type_ID(FK from Toy_Type) table

Why is it required ie Toy_Type_ID in the Toy table as we have box_ID in toy table which has already foreign key as Toy_Type_ID


We can get the color of a toy by Joining Toy_Table and Toy_box Table right ???
0
 

Author Comment

by:sainavya1215
Comment Utility
Can I eliminate Toy_Type_ID from Toys table ??? Arent we repeating toy_type_ID in Toys as well as in Box table....(Box table already has it)
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
>>My question was there is a table which is Toy which contains Toy_id,Toy_Description , Box_ID(FK from boxtable), Toy_Type_ID(FK from >>Toy_Type) table

perhaps you can't use the "box" attribute until the toy is assigned to a box?

do the different objects have different lifecycles?

ie is box data only kept for a year
whilst toy data is required for 5 years
for legal purposes?
0
 

Author Comment

by:sainavya1215
Comment Utility
Yes Ur right  for sure Toy table should have Box_ID in it to identify which box the toy belongs to make sense..thats why current model has it ..But Toy_type_ID exists in box table as well in Toy_Table..why cant we eliminate it in Toy_Table ? Current model has Toy_type_id in toy_table

thats my question right from the question posted
0
 

Author Comment

by:sainavya1215
Comment Utility
Box data is kept for ever
Toy data is kept forever
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
why cant we eliminate it in Toy_Table ?

From model:
Toys_box    Toy_box_id,Toy_type_id(FK from Toy_Type table)  ==  (Box which contains toys)

Toy         Toy_Id,Toy_box_ID(FK from Toys_Box) Toy_Name,Toy_Type_id(Fk from toy_Type table)

This is just speculation, here...:
Because there could be one toy box with different toy types in it (it's a bigger box -- containing superman and wonderwoman dolls -- a special offer pack) !
Its sold as a single toy_id:

Toy Type:
1   Wonderwoman -- Red Colour
2   Superman -- Red Colour

Toys_Box:
1 1
1 2
2 1
3 2

Toy Table:
1 1 1
1 1 2
2 2 1
3 3 2

You are keeping a history of every possible combination that has been sold.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Oh, and the next week somebody decided to sell the toys seperatly... Hence the two last lines in the Toy Table.
0
 

Author Comment

by:sainavya1215
Comment Utility
In my business scenario there is a Box with only one type of toy in it  (To be more clear Type is lets say material make (the material its made of) so I cant have all types of toys in one Box
0
 

Author Comment

by:sainavya1215
Comment Utility
so I thought why need the toy_type_id in the toys tabe again as Box already has it ..........But what i thought is the current model is right ..........for eg lets say we want to know the type of toy from toys_table?? what could be query in the current model?

as u can see we have to join all the 3 tables right............Instead of that performance wise it woul,d be better to have it in the toy table too ........so i feel what stands is good

What do u say
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
OK:
Toy_Type    Toy_type_ID,Toy_Color
                   1                Red
                   2                Blue

Toys_box    Toy_box_id,Toy_type_id(FK from Toy_Type table)  ==  (Box which contains toys)
                   1                 1
                   1                 2

Toy         Toy_Id,Toy_box_ID(FK from Toys_Box) Toy_Name,Toy_Type_id(Fk from toy_Type table)
               1           1                                                           1
               1           1                                                           2

But you can have multiple types making up a toy box...
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
then you can remove the toy type from whichever table is appropriate

i'd have thought the box shouldn't have it... (just from the name basically)

0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
What does the data, look like in the underlying tables ?
0
 

Author Comment

by:sainavya1215
Comment Utility
I think the way it is stands good due to the following posted earlier

so I thought why need the toy_type_id in the toys tabe again as Box already has it ..........But what i thought is the current model is right ..........for eg lets say we want to know the type of toy from toys_table?? what could be query in the current model?

as u can see we have to join all the 3 tables right............Instead of that performance wise it woul,d be better to have it in the toy table too ........so i feel what stands is good

What do u say ????? PERFORMANCE WISE REMOVING TOY_TYPE_ID FROM TOYS_TABLE IS NO GOOD AS UR COMPLICATING THE QUERY INSTEAD HAVE IT THE SAME WAY
0
 

Author Comment

by:sainavya1215
Comment Utility
as it stands if u want to know the type of the toy  only simple select statement is required.........

select toy_type_id from toys where toy_name = "xyz" but if u eliminate toy_type_id u have have a join query for 3 tables ............First when i posted i thought  of normalization later i realised 100% normalization is no good  so its better we have to follow sometimes other techniques keeping performance aspect in consideration
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Its not an issue just made by performance, it depends on the data underlying the data structure / (business rules for having a toys relationship to a toy box -> toy type.)

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:danblake
Comment Utility
as u can see we have to join all the 3 tables right............Instead of that performance wise it woul,d be better to have it in the toy table too ........so i feel what stands is good

What do u say ?

--> Depends on the data... but in principle....
 Joining tables (currently) takes longer (especially big tables) than a using a denormalised design ...
0
 

Author Comment

by:sainavya1215
Comment Utility
toyBOX

toyboxid  toy_type_id
1             1                (box 1 contains one type only)
2             2
3             1

Toy_Type
toy_type_id  toy_type_desc
1                 madeofWool
2                 made of cotton

Toys
Toy_id    toy_box_id  Toy_Desc     toy_type_id (AGAIN ITS REPATING HERE)
1                1            abc              1
2                1            bcd              1
3                2            xyz               2
0
 

Author Comment

by:sainavya1215
Comment Utility
based upon the data above I thought i could remove the toy_type_id from from Toys table as we have toy_box_id (where we can join and get type of toy)

but finally I am thinking current model is ok as single select statement required
0
 

Author Comment

by:sainavya1215
Comment Utility
what do u suggest keep it the same or remove it and join 3 tables or keeping it same join toys and toy_type table to get the toy_type
0
 

Author Comment

by:sainavya1215
Comment Utility
As u have seen the data

there are two options

1) Keep the model the same and GET Toy_Desciription joining 2 tables (ie   toys and Toy_type to get the toy_descriiption

           OR

2) REmove Toy_type_ID from Toys table as its repeating unnecessarily  and Join 3 tables ( TOys---Box ---- toy_Type to get the toy_description)

which one is correct and why ?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
IS the application an OLTP application or a OLAP Application ?

(This makes a difference -- I know it should not, in a perfect world..)
0
 

Author Comment

by:sainavya1215
Comment Utility
Well I am using asp.NET applicatiin with sql server 2000 as backend ............Normal scenario........Is this question so complicated?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 150 total points
Comment Utility
from a model viewpoint

you appear to have modelled the
relationships as  

1) Toy has a ToyType

2) Box contains Toys  

3) Box is for a ToyType

so to effect those constraints referentially then the physical representation is correct with
the duplicated toy type....

for a phsysical implementation YOU have to choose the trade off between
storage, performance
Business Logic (ie you know wether your business function/process could ever wish to break rule 3 , You've stated it wont!)


Do choice 1 join 2 tables....

I'm leaving this question

Good luck.
0
 

Author Comment

by:sainavya1215
Comment Utility
its like this

Toy has a toy_type

Box Contains toy which are of Toy_Type

toy_Type table contains toy_type_description

so u mean to say keep the toy_type_id in Toys_table.........Isnt this a normalization problem of repeating toy_type_id in Toys as well as in Box table
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 100 total points
Comment Utility
Complicated ?
Yes.

Have a look here...
http://www.sum-it.nl/cursus/dbdesign/english/techn040.htm
The reason for the difference....You need to test both models, and decide if this is right for you.

The table is perfect for an OLAP application, if it is OLTP not 'perfect' due to requring updates to dual tables.
If the number of OLTP updates > OLAP reads then you may wish to remove the non-required repeating field.
If OLAP > OLTP -- then keep it as is...

IF really interested in the underlying issues involved have a look at:
http://www.sql-server-performance.com/database_design.asp

You may want to copy/move the data from the three tables if an OLTP design to a seperate database (and table) in a denormalised form to assist with reporting.

0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
To truly give the ultimate question ....

why do you want to do this ?
0
 

Author Comment

by:sainavya1215
Comment Utility
do what ? The current model is like that ..........I was told to rectify if any probs ..........so got to know right
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
Then test both models, and write a report with all your findings...
  performance,
  storage,
  loading,
  reporting,
  OLTP / OLAP usage performance....

I'll be interested in reading it !
0
 

Author Comment

by:sainavya1215
Comment Utility
ok tell me something blake with these 3 tables only what exactly is the complexity in the question i had asked........i am still wondering :)

What if the toy_type_id is removed from toys? is it a blunder

what if toy_type-id is kept the same like that ? is this a problem

what is there to really do all the stuff u told me .
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
What if the toy_type_id is removed from toys? is it a blunder
---> Not necessarily.

what if toy_type-id is kept the same like that ? is this a problem
--> Not necessarily.
0
 

Author Comment

by:sainavya1215
Comment Utility
if its kept the same its a normalization prob (like u said before denormalized structure)

if its removed its Complex query ur making it a query of 3 table  join  

so which to follow i really dint understand
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
what is there to really do all the stuff u told me ?

                                 performance monitor,
                                 monitoring the speed of your app before/after the change,
                                 sql-server profiler to measure the difference in speed of the statments with without joins,
                                 compare a run to another run (such as with dedicated test tools)...
the list goes on and this is a question not easily answered....
0
 

Author Comment

by:sainavya1215
Comment Utility
Well without testing also it can be known

3 table join compared to two table join

In our case as lowfat said if its the same as of now without removing i suppose its a two way join with toys and toy_type tables which is for sure better than removing it ....... so its understood
0
 

Author Comment

by:sainavya1215
Comment Utility
U hv to defintely join to get the description either Two tables or 3 tables
0
 

Author Comment

by:sainavya1215
Comment Utility
Anyway thanks dan and lowfat for the effort...........I will proceed with keeping it as it is
0
 

Author Comment

by:sainavya1215
Comment Utility
Thanks for solving the questions unAnswered  Dan .......I appreciate that
0
 

Author Comment

by:sainavya1215
Comment Utility
hi,
I had posted a question about a trigger action about the table discussed earlier

Here it is

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20935080.html
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now