如何设计数据库,并用代码实现岗位配置图
请问如何根据数据库,并用代码提取数据库中的信息自动生成岗位配置图
问题1:这样的数据库如何设计,主要是上下级的关系
问题2:因为经常有人员调整,如何维护好人员信息后自动的生成这样的岗位配置图,最后用C#来实现
[解决办法]
剩下的就是C#代码什么展现出来了
---表结构:
Employee(EmplNo,EmplName,PositionNo,Enabled)
Position(PositionNo,Name,Enabled)
--查询
select EmplNo,EmplName,b.Name as PositionName
from Employee a
inner join Position b on a.PositionNo=b.PositionNo
order by a.PositionNo
--请参考
/****** Object: Table [dbo].[HR_Org] Script Date: 10/30/2013 08:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HR_Org](
[Org_Id] [int] IDENTITY(1,1) NOT NULL,
[Org_Pid] [int] NOT NULL,
[Org_No] [varchar](10) NOT NULL,
[Org_Name] [varchar](100) NOT NULL,
[Org_Name_En] [varchar](100) NOT NULL,
[Org_Level] [tinyint] NOT NULL,
[Is_Enabled] [bit] NOT NULL,
[Sort_Id] [int] NOT NULL,
[Is_Company] [bit] NOT NULL,
[Org_Manager] [varchar](200) NOT NULL,
[Update_User] [varchar](20) NOT NULL,
[Update_Date] [datetime] NOT NULL,
CONSTRAINT [PK_HR_Org] PRIMARY KEY CLUSTERED
(
[Org_Id] 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
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组织ID号(主键)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上级组织编号(根节点为0)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Pid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组织编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_No'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组织名称(唯一索引)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组织英文名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Name_En'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'等级' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Level'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'启用否(1:启用;0:停用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Is_Enabled'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Sort_Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否为公司(1:是;0:否)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Is_Company'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门负责人(多人","分隔表示并;"_"分隔表示串;)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Org_Manager'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Update_User'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org', @level2type=N'COLUMN',@level2name=N'Update_Date'
GO
EXEC sys.sp_addextendedproperty @name=N'Remark', @value=N'组织架构表;要考虑集团、多家子公司的情况' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HR_Org'
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Org_Name_En] DEFAULT ('') FOR [Org_Name_En]
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Sort_Id] DEFAULT ((1)) FOR [Sort_Id]
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Is_Company] DEFAULT ((0)) FOR [Is_Company]
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Org_Manager] DEFAULT ('') FOR [Org_Manager]
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Update_User] DEFAULT ('') FOR [Update_User]
GO
ALTER TABLE [dbo].[HR_Org] ADD CONSTRAINT [DF_HR_Org_Update_Date] DEFAULT (getdate()) FOR [Update_Date]
GO