Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Design of Master / Child Tables ??? Expand / Collapse
Author
Message
Posted Monday, October 5, 2009 6:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 2:04 PM
Points: 29, Visits: 124
Hi All,

i have a scenario where need to maintain 3 levels of information like

Market1
Product1
Model1
Model2
Product2
Model1
Model2
Model3
Model4
Market2
Product1
Model1
Model2
Market3
Product1
Model1

right now i am having 3 tables
1.Master_Market
2.Master_Product (FK to Primary of Master_Market )
3.Master_Model (FK to Primary of Master_Product )

but user will be selecting Master_Model level info, i am saving Master_Model internal id (PK) into database.

but when ever i want to load particular Models for a product under a market i need to join 3 tables with two inputs i.e. Market & Product.

can any one have better solution than this
Post #797817
Posted Monday, October 5, 2009 7:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
If the following is true...
A Market can have many Products, but a Product can be in only one market
A Product can have many Models, but a Model can be in only one market

... then it looks as if your database models the business world, and is in 3rd normal form, so there isn't a better solution.



Post #797860
Posted Monday, October 5, 2009 7:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
Why 3 tables? Isn't market in the Market_Product table? Can't you join those 2 tables?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #797896
Posted Monday, October 5, 2009 10:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 2:04 PM
Points: 29, Visits: 124
Hi Steve,

Could you please explain in more details.

as Ian Scarlett said the bellow two are true
A Market can have many Products, but a Product can be in only one market - TRUE
A Product can have many Models, but a Model can be in only one market - TRUE

even i am also looking to reduce the count of the tables. at max i can have 1500 to 2000 records at model level

Thanks Ian & Steve
Post #798312
Posted Monday, October 5, 2009 10:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:46 AM
Points: 1,593, Visits: 2,661

1.Master_Market
2.Master_Product (FK to Primary of Master_Market )
3.Master_Model (FK to Primary of Master_Product )

but user will be selecting Master_Model level info, i am saving Master_Model internal id (PK) into database.


There's nothing wrong with this design and as mentioned it's in 3rd normal form.


but when ever i want to load particular Models for a product under a market i need to join 3 tables with two inputs i.e. Market & Product.


Steve was saying there's "Market_ID" in Master_Product table, hence you don't have to link 3 tables instead you can get the result by joining Master_Product and Master_Model tables. But yes, To get Market name you will have to join Master_Market as well.

Just an opinion,

Why don't you create a VIEW, joining all the 3 tables and you can simple query that VIEW like a single table..?
Post #798318
Posted Tuesday, October 6, 2009 3:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
Don't compromise the design of your database just to have fewer tables and make the SQL easier/shorter... you will just be storing up problems that will have the potential to bite you later on.

If you think the SQL is to complex, then the advice from the previous post to create a VIEW is good advice.



Post #798371
Posted Tuesday, October 6, 2009 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 15, 2012 2:04 PM
Points: 29, Visits: 124
Thank You all,

after all your inputs i am sticking with 3 tables and a view to populate the Models.

There is another argument that is going on my team , need some input on this

Sample data:

Table Name:Master_Market
mid--Market
101-USA
102-UK
103-INA

Table Name:Master_Product (FK to Primary of Master_Market )
pid--mid--Product
1001--101--P1
1002--101--P2
1003--102--P1
1004--103--P3

Table Name:Master_Model (FK to Primary of Master_Product )
id--pid--Model (id-Identity,PK - [pid-model] )
1--1001--M1
2--1001--M2
3--1002--M1
4--1002--M3
5--1003--M1
6--1004--M2
7--1004--M3
8--1004--M4

if i want to populate models for INA & P3 by using the VIEW the input should be
1. Should i pass 'INA' & 'P3' as string. i.e. actual values
OR
2. Should i pass '103' & '1004' as string. i.e. actual values

here the question should we use internal id or the actual values ? when we are fetching the data ?

Thank You
Post #798387
Posted Tuesday, October 6, 2009 7:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
You always want to pass in the data at the lowest level. I assume that you have indexed the mid and pid in the middle table, you'd use those for querying.

When you say pass in, I assume you mean that you have a stored procedure. That's the best way for you to write standard queries, and if you are calling these with .NET stored procedure objects, you prevent SQL Injection issues.

In that case, to get the models for a product you'd do something like


create procedure GetModels
@productID int
as

select pid, id, model
from master_model
where pid = @ProductID

return


A view is like a table. You don't "pass" in values. If you had a view, say called ModelView, you'd query it

select pid, id, model
from modelview
where pid = 1003

You could use that view in a stored procedure, as another table.

If you want to insert values, note that you cannot insert through a view to more than one table. Which means you'd need 3 separate inserts through the view to populate all 3 tables. That might not be a big deal as you'll typically populate products less than models, and markets less than products.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #798492
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse