3 SQL Table Referential integrity



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  ....
sainavya1215Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
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
dfiala13Commented:
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
sainavya1215Author Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LowfatspreadCommented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
Lets forget about the box color though....
0
LowfatspreadCommented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
LowfatspreadCommented:
>>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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
Box data is kept for ever
Toy data is kept forever
0
danblakeCommented:
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
danblakeCommented:
Oh, and the next week somebody decided to sell the toys seperatly... Hence the two last lines in the Toy Table.
0
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
danblakeCommented:
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
LowfatspreadCommented:
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
danblakeCommented:
What does the data, look like in the underlying tables ?
0
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
danblakeCommented:
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
danblakeCommented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
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
danblakeCommented:
IS the application an OLTP application or a OLAP Application ?

(This makes a difference -- I know it should not, in a perfect world..)
0
sainavya1215Author Commented:
Well I am using asp.NET applicatiin with sql server 2000 as backend ............Normal scenario........Is this question so complicated?
0
LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sainavya1215Author Commented:
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
danblakeCommented:
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
danblakeCommented:
To truly give the ultimate question ....

why do you want to do this ?
0
sainavya1215Author Commented:
do what ? The current model is like that ..........I was told to rectify if any probs ..........so got to know right
0
danblakeCommented:
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
sainavya1215Author Commented:
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
danblakeCommented:
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
sainavya1215Author Commented:
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
danblakeCommented:
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
sainavya1215Author Commented:
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
sainavya1215Author Commented:
U hv to defintely join to get the description either Two tables or 3 tables
0
sainavya1215Author Commented:
Anyway thanks dan and lowfat for the effort...........I will proceed with keeping it as it is
0
sainavya1215Author Commented:
Thanks for solving the questions unAnswered  Dan .......I appreciate that
0
sainavya1215Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.