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


suggestions on one to may relationship in the data processing


suggestions on one to may relationship in the data processing

Author
Message
Gangadhara MS
Gangadhara MS
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 840
Apology,pls consider this and suggest me.
I need some design help here.

This is my source input data which i need to select based on condition which is given by business.

create table partners(
parter_id varchar(10),
start_date datetime,
end_date datetime,
competency_name varcgar(50))


insert into partners ( 'P1','1/14/2013','2/5/2014','Gold Cloud Productivity')
insert into partners ( 'P1','5/10/2013','2/6/2014','Gold Small and MidMarket Cloud Solutions ')
insert into partners ( 'P1','7/14/2013','9/19/2014','Silver Small and MidMarket Cloud Solutions')
insert into partners ( 'P2','9/14/2013','10/15/2014','Gold Cloud Productivity')

and i have config table (not sure how it will be but initial my thought of design ) i will have this information from business which are the competency should exist to select particular partner and they specify the condition to select meaning( if partner should be more than one or 2 competency to be eligible for payment)

create table mastercompetency
(program varchar(10),
SKUgroup varchar(10),
competencyname varchar(50))

insert into mastercompetency values('DIRECT','SKG1','Gold Cloud Productivity ')
insert into mastercompetency values('DIRECT','SKG1','Silver Cloud Productivity ')
insert into mastercompetency values('EA','SKG2','Gold Small and MidMarket Cloud Solutions ')
insert into mastercompetency values('EA','SKG1','Silver Small and MidMarket Cloud Solutions ')


Now i need to select the Data from source which should match criteria from business to select eligible partner, how to design this.

If input data matches (Gold Cloud Productivity and Gold Small and MidMarket Cloud Solutions) OR
(Silver Small and MidMarket Cloud Solutions ) that partner need be selected from input source data for payment

The condition i said is one example, it may have any combinations of competencies to be consider as eligible partner.

Hope its clear now.

Thanks for all your help till now.
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 8494
So P1 overlaps in Small / MidMarket Cloud Solutions?
Gold and Silver - is this related to Partner? Or to the Service being provided?

insert into partners ( 'P1','5/10/2013','2/6/2014','Gold Small and MidMarket Cloud Solutions ')
insert into partners ( 'P1','7/14/2013','9/19/2014','Silver Small and MidMarket Cloud Solutions')

Direct and EA - are these related to SKU Group or the Service being provided?

insert into mastercompetency values('DIRECT','SKG1','Gold Cloud Productivity ')
insert into mastercompetency values('DIRECT','SKG1','Silver Cloud Productivity ')
insert into mastercompetency values('EA','SKG2','Gold Small and MidMarket Cloud Solutions ')
insert into mastercompetency values('EA','SKG1','Silver Small and MidMarket Cloud Solutions ')

I would spend effort on normalizing the tables first, then the query.
This could be IN (Gold Cloud Productivity,Gold Small and MidMarket Cloud Solutions) OR (Silver Small and MidMarket Cloud Solutions ),
or (MidMarket Cloud Solutions) Level IN (Gold,Silver) AND (Cloud Productivity AND Level = (Gold))
depending on how you normalize the tables.

If input data matches (Gold Cloud Productivity and Gold Small and MidMarket Cloud Solutions) OR
(Silver Small and MidMarket Cloud Solutions ) that partner need be selected from input source data for payment

To me, you have a Partners table, then one that may have many Partner / Services Provided, and a Services table.
I'd probably go as far as Level (Gold, Silver) having a table too.
Keys - are important. Things like Gold and Silver are more of a Description, and are subject to change.
You need to be able to allow for flexibility in the future.
Gangadhara MS
Gangadhara MS
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 840
Gold and Silver - is this related to Partner? Or to the Service being provided?

Its related to partner which they own it after meeting some sales criteria.


Direct and EA - are these related to SKU Group or the Service being provided?

Its for a different program of type Direct and EA and related to SKU group.

Do i need to add any further here ? not sure about your suggestion Greg ?

Thanks for your help.
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 8494
Gangadhara MS (6/26/2014)
Gold and Silver - is this related to Partner? Or to the Service being provided?

Its related to partner which they own it after meeting some sales criteria.
So it sounds like it should be modeled as an attribute of the Partner, not directly into the Services they provide.
i.e. - the Services table does not have this, only the different Services that can be related to any given Partner.


Direct and EA - are these related to SKU Group or the Service being provided?

Its for a different program of type Direct and EA and related to SKU group.
Ask yourself if this should be an attribute of the Partner.
Sounds kind of like where we used to have Direct Customers we sold to as individuals, and stores that we stocked.

Do i need to add any further here ? not sure about your suggestion Greg ?

Thanks for your help.


So I hope you are seeing some of this, like how the Partner provides Services, and the Gold and Silver are related to the Partner.
It sounds like any Services a Partner offers are Gold or Silver based on the Partner.
So if their volume increases, or decreases, you need to only change the attribute of the Partner, not all the Services.
Likewise, a table with the Services should only need to list the Services available, not have a Gold and Silver level Service listed.
Make sense?
Gangadhara MS
Gangadhara MS
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 840
yes i will list all the competency in one table as mentionedbefore but how do we select the partner for multiple conditions met.

I mean i need to select a partner who is having multiple competency level (Gold Cloud Productivity AND Gold Small and MidMarket Cloud Solutions OR Silver Cloud Productivity)

but i can't list this possibality of selection criteria for all the values.

If u give some more info will be helpful Greg..thanks for for your time
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 8494
Could also be written as a CTE.
Moving Gold and Silver to the partners table, or other changes to more normalize (keys instead of descriptions) will change things.

select
[partner_id]
,[competency_name]
,[program]
,[SKUgroup]
,[competencyname]
,[start_date]
,[end_date]
from [dbo].[partners] a
inner join [dbo].[mastercompetency] b on a.[competency_name]=b.[competencyname]
where
[partner_id] IN (
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] IN ('Gold Cloud Productivity','Gold Small and MidMarket Cloud Solutions')
group by
[partner_id]
HAVING count([competency_name]) = 2
UNION
select
[partner_id]
from
[dbo].[partners]
where
[competency_name] = 'Silver Small and MidMarket Cloud Solutions')
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