October 5, 2009 at 6:29 am
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
October 5, 2009 at 7:15 am
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.
October 5, 2009 at 7:52 am
Why 3 tables? Isn't market in the Market_Product table? Can't you join those 2 tables?
October 5, 2009 at 10:24 pm
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
October 5, 2009 at 10:46 pm
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..?
October 6, 2009 at 3:06 am
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.
October 6, 2009 at 3:39 am
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
October 6, 2009 at 7:25 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply