Solved

3 SQL Table Referential integrity

Posted on 2004-03-20
44
216 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
ID: 10643060
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
ID: 10643066
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
ID: 10643113
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10643328
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
ID: 10643345
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
ID: 10643353
Lets forget about the box color though....
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10644867
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
ID: 10647036
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
ID: 10647040
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
ID: 10647216
>>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
ID: 10647263
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
ID: 10647361
Box data is kept for ever
Toy data is kept forever
0
 
LVL 13

Expert Comment

by:danblake
ID: 10681227
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
ID: 10681440
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
ID: 10681460
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
ID: 10681498
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
ID: 10681505
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
ID: 10681533
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
ID: 10681551
What does the data, look like in the underlying tables ?
0
 

Author Comment

by:sainavya1215
ID: 10681558
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
ID: 10681608
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
ID: 10681612
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
 
LVL 13

Expert Comment

by:danblake
ID: 10681652
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
ID: 10681684
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
ID: 10681701
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
ID: 10681728
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
ID: 10681798
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
ID: 10681832
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
ID: 10681874
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
ID: 10681889
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
ID: 10681939
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
ID: 10681980
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
ID: 10681997
To truly give the ultimate question ....

why do you want to do this ?
0
 

Author Comment

by:sainavya1215
ID: 10682015
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
ID: 10682034
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
ID: 10682065
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
ID: 10682086
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
ID: 10682119
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
ID: 10682122
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
ID: 10682143
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
ID: 10682156
U hv to defintely join to get the description either Two tables or 3 tables
0
 

Author Comment

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

Author Comment

by:sainavya1215
ID: 10682214
Thanks for solving the questions unAnswered  Dan .......I appreciate that
0
 

Author Comment

by:sainavya1215
ID: 10698919
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Delete Query 9 30
SQL Server 2012 r2 - Varible Table 3 23
What's wrong with this T-SQL Foreign Key? 7 41
Whats wrong in this query - Select * from tableA,tableA 11 28
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

808 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