Custom CRM Account/Manager - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Custom CRM Account/Manager

Author

Topic

sfoutty
Starting Member

3 Posts

Posted - 2014-06-05 : 12:04:05

I've been tasked to create a "SMALL" custom crm application. I have most of the app created but I'm stumbling on how to associate windows users as account managers to customer accounts.

Anyone got any suggestions?

SUPER Simple diagram

Account (table)
_________
AccountID (pk)
BMAccountManagerID

User (table)
__________
WindowsID (I have a default value of (suser_sname() that pulls the windows username.
FirstName
LastName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 14:31:07

If there is only ever zero or one account manager per account, then you can put the UserID in the Account table. Otherwise you'll want to make a junction table to allow multiple account managers.

sfoutty
Starting Member

3 Posts

Posted - 2014-06-05 : 14:32:50

Any suggestions on how to create the junction table?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 15:20:38

You only need two columns: AccountID and UserID - Make that your PK or, at least, put a Unique Constraint on those columns.

After that, it depends if you need any auditability and tracing. If so, then you may need to add some more column(s).

sfoutty
Starting Member

3 Posts

Posted - 2014-06-06 : 14:37:31

Like This

CREATE TABLE [dbo].[AM2ACT](
[AM2ACTID] [int] NOT NULL,
[WindowsID] [nchar](10) NULL,
[BMEAccountManagerID] [nchar](10) NULL,
[EMEAccountManagerID] [nchar](10) NULL,
[IMEAccountManagerID] [nchar](10) NULL,
[RECAccountManagerID] [nchar](10) NULL,
[RMEAccountManagerID] [nchar](10) NULL,
[THEAccoutManager] [nchar](10) NULL,
CONSTRAINT [PK_AM2ACT] PRIMARY KEY CLUSTERED
(
[AM2ACTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_WindowsID] UNIQUE NONCLUSTERED
(
[WindowsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_BMEAccountManagerID] FOREIGN KEY([BMEAccountManagerID])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_BMEAccountManagerID]
GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_EMEAccountManagerID] FOREIGN KEY([EMEAccountManagerID])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_EMEAccountManagerID]
GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_IMEAccountMangerID] FOREIGN KEY([IMEAccountManagerID])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_IMEAccountMangerID]
GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_RECAccountManagerID] FOREIGN KEY([RECAccountManagerID])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_RECAccountManagerID]
GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_RMEAccountManagerID] FOREIGN KEY([RMEAccountManagerID])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_RMEAccountManagerID]
GO

ALTER TABLE [dbo].[AM2ACT] WITH CHECK ADD CONSTRAINT [FK_WindowsID_THEAccountManagerID] FOREIGN KEY([THEAccoutManager])
REFERENCES [dbo].[AM2ACT] ([WindowsID])
GO

ALTER TABLE [dbo].[AM2ACT] CHECK CONSTRAINT [FK_WindowsID_THEAccountManagerID]
GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-09 : 11:37:24

What are all those account manager IDs for?

If you have different classifications of account managers, then I'd assume you have a table to define those classifications. If so, just add that classification to the table, that way you don't have to have multiple columns for, in essence, the same thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources