Link to home
Start Free TrialLog in
Avatar of sainavya1215
sainavya1215

asked on

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  ....
Avatar of arbert
arbert

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?
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.
Avatar of sainavya1215

ASKER

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?????
Avatar of Lowfatspread
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...
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
Lets forget about the box color though....
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.
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 ???
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)
>>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?
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
Box data is kept for ever
Toy data is kept forever
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.
Oh, and the next week somebody decided to sell the toys seperatly... Hence the two last lines in the Toy Table.
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
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
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...
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)

What does the data, look like in the underlying tables ?
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
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
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.)

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 ...
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
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
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
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 ?
IS the application an OLTP application or a OLAP Application ?

(This makes a difference -- I know it should not, in a perfect world..)
Well I am using asp.NET applicatiin with sql server 2000 as backend ............Normal scenario........Is this question so complicated?
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To truly give the ultimate question ....

why do you want to do this ?
do what ? The current model is like that ..........I was told to rectify if any probs ..........so got to know right
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 !
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 .
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.
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
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....
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
U hv to defintely join to get the description either Two tables or 3 tables
Anyway thanks dan and lowfat for the effort...........I will proceed with keeping it as it is
Thanks for solving the questions unAnswered  Dan .......I appreciate that
hi,
I had posted a question about a trigger action about the table discussed earlier

Here it is

https://www.experts-exchange.com/questions/20935080/Transactional-trigger-action.html