SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design of Master / Child Tables ???


Design of Master / Child Tables ???

Author
Message
raju.tanneeru
raju.tanneeru
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2847 Visits: 7055
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.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84257 Visits: 19223
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
My Blog: www.voiceofthedba.com
raju.tanneeru
raju.tanneeru
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Joy Smith San
Joy Smith San
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4026 Visits: 3200

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..?
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2847 Visits: 7055
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.



raju.tanneeru
raju.tanneeru
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84257 Visits: 19223
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search