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 ....
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.
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?????
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...
does toy_type really only give a colour?
in which case the
toy can be red in a white box ?
just a comment...
ASKER
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
ASKER
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.
unless its there for some performance related reason.
ASKER
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 ???
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 ???
ASKER
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?
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?
ASKER
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
thats my question right from the question posted
ASKER
Box data is kept for ever
Toy data is kept forever
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.
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.
ASKER
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
ASKER
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
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...
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)
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 ?
ASKER
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
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
ASKER
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
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 ...
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 ...
ASKER
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
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
ASKER
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
but finally I am thinking current model is ok as single select statement required
ASKER
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
ASKER
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 ?
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..)
(This makes a difference -- I know it should not, in a perfect world..)
ASKER
Well I am using asp.NET applicatiin with sql server 2000 as backend ............Normal scenario........Is this question so complicated?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To truly give the ultimate question ....
why do you want to do this ?
why do you want to do this ?
ASKER
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 !
performance,
storage,
loading,
reporting,
OLTP / OLAP usage performance....
I'll be interested in reading it !
ASKER
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
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.
---> Not necessarily.
what if toy_type-id is kept the same like that ? is this a problem
--> Not necessarily.
ASKER
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
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....
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....
ASKER
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
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
ASKER
U hv to defintely join to get the description either Two tables or 3 tables
ASKER
Anyway thanks dan and lowfat for the effort...........I will proceed with keeping it as it is
ASKER
Thanks for solving the questions unAnswered Dan .......I appreciate that
ASKER
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
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
Have you looked at the data to see if a toys_box ever has a different toy_type than it's associated toy?