薪资数据库表设计.docx

(local) DocumentationQK365_RemunerationServer(local)AuthorAdministratorCreated31 三月 2016 11:03File PathC:UsersAdministratorDocumentsMy Database Documentation薪资数据库表设计-2016-03-31T11-03-03.docxTable of ContentsTable of Contents2 (local)3 User databases5 QK365_Remuneration Database6 Tables9 dbo.HR_ConfigTax10 dbo.HR_ConfigTaxRule11 dbo.HR_ConfigType13 dbo.HR_CusEmpInfo14 dbo.HR_CusSaleSlip 16 dbo.HR_EmpAttendanceStatistics18 dbo.HR_FullRoomNumber20 dbo.HR_SalaryCalc22 dbo.HR_SalaryCalcConfig23 dbo.HR_SalaryStatistics24Project (local) (local)Databases(1) QK365_RemunerationServer PropertiesPropertyValueProductMicrosoft SQL ServerVersion12.0.2000.8Language中文(简体，中国)PlatformNT x64EditionDeveloper Edition (64-bit)Processors4OS Version6.1 (7601)Physical Memory8068Is ClusteredFalseRoot DirectoryD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLCollationChinese_PRC_CI_ASServer SettingsPropertyValueDefault data file pathD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATADefault backup file pathD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackupDefault log file pathD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATARecovery Interval (minutes)0Default index fill factor0Default backup media retention0Compress BackupFalseAdvanced Server SettingsPropertyValueFull text upgrade option2Locks0Nested triggers enabledTrueAllow triggers to fire othersTrueDefault languageSimplified ChineseNetwork packet size4096Default fulltext language LCID2052Two-digit year cutoff2049Remote login timeout10Cursor threshold-1Max text replication size65536Parallelism cost threshold5Scan for startup procsFalseTransform noise wordsFalseBlocked process threshold0Filestream access levelFalseOptimize for ad hoc workloadsFalseAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 4 of 25Project (local) User databases User databasesDatabases(1) QK365_RemunerationAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 5 of 25Project (local) User databases QK365_Remuneration QK365_Remuneration DatabaseDatabase PropertiesPropertyValueSQL Server VersionSQL Server 2014Compatibility LevelSQL Server 2012Last backup time03/31/2016Last log backup time-Creation date03 30 2016 1Users4Database Encryption EnabledFalseDatabase Encryption AlgorithmNoneDatabase size8.06 MBUnallocated space2.20 MBDatabase OptionsPropertyValueCompatibility Level110Database collationChinese_PRC_CI_ASRestrict accessMULTI_USERIs read-onlyFalseAuto closeFalseAuto shrinkFalseDatabase statusONLINEIn standbyFalseCleanly shutdownFalseSupplemental logging enabledFalseSnapshot isolation stateOFFRead committed snapshot onFalseRecovery modelFULLPage verify optionCHECKSUMAuto create statisticsTrueAuto update statisticsTrueAuto update statistics asynchronouslyFalseANSI NULL defaultFalseANSI NULL enabledFalseANSI padding enabledFalseANSI warnings enabledFalseArithmetic abort enabledFalseConcatenating NULL yields NULLFalseNumeric roundabort enabledFalseQuoted Identifier OnFalseRecursive triggers enabledFalseClose cursors on commitFalseLocal cursors by defaultFalseFulltext enabledTrueTrustworthyFalseDatabase chainingFalseForced parameterizationFalseMaster key encrypted by serverFalsePublishedFalseSubscribedFalseMerge publishedFalseIs distribution databaseFalseSync with backupFalseService broker GUID575bb83f-db53-4095-8cc4-966f8f12379fService broker enabledFalseLog reuse waitLOG_BACKUPDate correlationFalseCDC enabledFalseEncryptedFalseHonor broker priorityFalseDefault languageSimplified ChineseDefault fulltext language LCID2052Nested triggers enabledTrueTransform noise wordsFalseTwo-digit year cutoff2049ContainmentNONETarget recovery time0Database ownersaFilesNameTypeSizeMaxsizeAutogrowthFile NameQK365_RemunerationData5.00 MBunlimited1.00 MBD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAQK365_Remuneration.mdfQK365_Remuneration_logLog3.06 MB2048.00 GB10.00 percentD:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAQK365_Remuneration_log.ldfAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 8 of 25Project (local) User databases QK365_Remuneration Tables TablesObjectsNamedbo.HR_ConfigTax配置项表dbo.HR_ConfigTaxRule配置项明细规则表dbo.HR_ConfigType配置设定类型表dbo.HR_CusEmpInfo员工信息表dbo.HR_CusSaleSlip 销售单数表dbo.HR_EmpAttendanceStatistics员工月度考勤计算结果表dbo.HR_FullRoomNumber满房数表dbo.HR_SalaryCalc薪资计算逻辑基表dbo.HR_SalaryCalcConfig公司职位薪资计算逻辑关系表dbo.HR_SalaryStatistics员工月度薪资计算结果表Author: AdministratorCopyright 2016 - All Rights ReservedPage 9 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_ConfigTax dbo.HR_ConfigTaxMS_Description配置项表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created11:04:47 2016年3月29日Last Modified10:59:45 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsConfigTaxId配置项标识uniqueidentifier16FalseConfigTypeId配置设定类型标识uniqueidentifier16TrueConfigName配置项名称nvarchar(50)100FalseDescription描述varchar(250)250TrueCreateTime创建时间datetime8FalseModifyTime修改时间datetime8FalseState状态bit1FalseCreator创建人nvarchar(50)100TrueModifier更新人nvarchar(50)100TrueIndexesKeyNameColumnsUniquePK_taxCategoryConfigTaxIdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 10 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_ConfigTaxRule dbo.HR_ConfigTaxRuleMS_Description配置项明细规则表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created17:20:09 2016年3月30日Last Modified10:59:51 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsDefaultConfigTaxRuleId配置项明细标识uniqueidentifier16FalseCompanyId公司编号nvarchar(50)100FalseConfigTaxId配置项标识uniqueidentifier16FalseTaxRuleName规则名称nvarchar(50)100FalseTaxCompanyValue公司配置值nvarchar(50)100FalseTaxValue比例参数（个人）varchar(50)50False(0)UpperLimitint4TrueLowLimitint4TrueDescription描述nvarchar(250)500TrueCreateTime创建时间datetime8FalseModifyTime更新时间datetime8Falsestatebit1FalseCreator创建人nvarchar(50)100TrueModifier修改人nvarchar(50)100TrueNumberTimes统计迟到早退次数int4True(0)IndexesKeyNameColumnsUniquePK_TaxRuleConfigTaxRuleIdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 12 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_ConfigType dbo.HR_ConfigTypeMS_Description配置设定类型表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created10:15:03 2016年3月30日Last Modified10:59:57 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsConfigTypeId配置类型(比例配置，销售配置)uniqueidentifier16FalseConfigTypeName配置名称nvarchar(50)100FalseCreateTimedatetime8FalseModifyTimedatetime8FalseCreatornvarchar(50)100TrueModifiernvarchar(50)100TrueIndexesKeyNameColumnsUniquePK_ConfigTypeConfigTypeIdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 13 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_CusEmpInfo dbo.HR_CusEmpInfoMS_Description员工信息表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created17:19:28 2016年3月30日Last Modified11:00:01 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsCustomerId主键uniqueidentifier16FalseCompanyId公司主键int4FalsePositionId职位主键int4FalseEmployeeNo员工编号varchar(20)20FalseEmployeeName员工名称nvarchar(50)100FalseJoinDate入职日期datetime8FalseRegularDate转正日期datetime8FalseLeaveDate离职日期datetime8FalseSocialDate社保起缴年月datetime8FalseProvidFundDate公积金缴纳起缴年月datetime8FalseHousehold户籍nvarchar(50)100FalseHouseFundBase公积金基数decimal(18,2)9TrueSocialBase社保基数decimal(18,2)9FalseBasicSalary基本工资decimal(18,2)9TrueSales销售单数int4TrueFullRoomNumber满房数decimal(18,1)9TrueProbationSalary试用期工资decimal(18,2)9TrueStatebit1TrueCreateTime创建时间datetime8FalseModifyTime修改日期datetime8FalseCreator创建人nvarchar(50)100TrueModifier修改人nvarchar(50)100TrueIndexesKeyNameColumnsUniquePK_CusEmpInfoCustomerIdTrueIX_EmployeeNOEmployeeNoTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 15 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_CusSaleSlip dbo.HR_CusSaleSlip MS_Description销售单数表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created11:09:28 2016年3月30日Last Modified11:00:05 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsId主键uniqueidentifier16FalseCustomer客户nvarchar(50)100FalseAdress地址nvarchar(50)100TrueContractDate签约时间datetime8FalseLoanType贷款类型nvarchar(50)100FalseloansCategory贷款种类nvarchar(50)100FalseNumber单数decimal(18,1)9TrueTerm期限datetime8TruePayMethod支付方式varchar(50)50FalseContractTerm合同期限datetime8FalseRetirementDate退租日期datetime8TrueHouseAdmin房管员nvarchar(50)100FalseManager管理经理nvarchar(50)100FalseServiceCenter服务中心nvarchar(50)100FalseServiceCompany售前所属分公司nvarchar(50)100FalseLandlord地主经理nvarchar(50)100FalseLandlordServiceCenter地主服务中心nvarchar(50)100FalseLandlordServiceCompany售后所属分公司nvarchar(50)100FalseReMark备注nvarchar(250)500TrueCreateTimedatetime8FalseModifyTimedatetime8FalseCreatornvarchar(50)100TrueModifiernvarchar(50)100TrueStatebit1FalseIndexesKeyNameColumnsUniquePK_Table_1_2IdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 17 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_EmpAttendanceStatistics dbo.HR_EmpAttendanceStatisticsMS_Description员工月度考勤计算结果表PropertiesPropertyValueCollationChinese_PRC_CI_ASHeapTrueRow Count ()0Created11:10:28 2016年3月30日Last Modified11:00:10 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsDefaultCustomerId主键uniqueidentifier16FalseEmployeeNo员工编号varchar(20)20FalseLater迟到(总分钟)数(汇总)decimal(18,2)9True(0)Early早退(总分钟)decimal(18,2)9True(0)Absent旷工小时数decimal(18,2)9True(0)Overtime加班小时数decimal(18,2)9True(0)HolidayOvertime节假日小时数decimal(18,2)9True(0)SickLeave病假小时数decimal(18,2)9True(0)CompassionateLeave事假小时数decimal(18,2)9True(0)YearHoliday年假decimal(18,2)9TrueMarriageLeave婚假decimal(18,2)9True(0)MaternityLeave产假小时数decimal(18,2)9True(0)Funeralleave丧假小时数decimal(18,2)9True(0)DaysOff调休假decimal(18,2)9True(0)FailureCard未打卡小时数decimal(18,2)9True(0)Official公出decimal(18,2)9TrueInAttendance应出勤decimal(18,2)9TrueActualAttendance实际出勤decimal(18,2)9TrueBeginTimedatetime8FalseEndTimedatetime8FalseYMonthchar(6)6FalseIndexesNameColumnsUniqueIX_Table_1EmployeeNoTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 19 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_FullRoomNumber dbo.HR_FullRoomNumberMS_Description满房数表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created10:15:52 2016年3月30日Last Modified11:00:14 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsIduniqueidentifier16FalseRoomAdressnvarchar(50)100FalseContractBeginDate合同开始日期datetime8FalseContractEndDate合同结束日期datetime8FalseServiceCenter房间所属服务中心nvarchar(50)100FalseCompany房间所属分公司nvarchar(50)100FalseHouseAdmin房管员nvarchar(50)100FalseCreateTimedatetime8FalseModifyTimedatetime8FalseCreatornvarchar(50)100TrueModifiernvarchar(50)100TrueState是否可用(0:可用,1：不可用)bit1FalseIndexesKeyNameColumnsUniquePK_FullRoomNumberIdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 21 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_SalaryCalc dbo.HR_SalaryCalcMS_Description薪资计算逻辑基表PropertiesPropertyValueCollationChinese_PRC_CI_ASHeapTrueRow Count ()0Created17:22:50 2016年3月30日Last Modified11:00:30 2016年3月31日ColumnsNameData TypeMax Length (Bytes)Allow NullsSalaryCalcId薪资计算逻辑主键uniqueidentifier16FalseNameSpace命名空间varchar(250)250FalseCalculateClass计算类varchar(50)50FalsePositionCategorynvarchar(50)100TrueCreateTimedatetime8FalseModifyTimedatetime8FalseCreatornvarchar(50)100TrueModifiernvarchar(50)100TrueState是否可用(0:可用,1：不可用)bit1FalseAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 22 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_SalaryCalcConfig dbo.HR_SalaryCalcConfigMS_Description公司职位薪资计算逻辑关系表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created17:23:34 2016年3月30日Last Modified11:00:33 2016年3月31日ColumnsKeyNameData TypeMax Length (Bytes)Allow NullsSalaryCalcConfigId制定部门,制定岗位的薪资计算逻辑标识uniqueidentifier16FalseCompanyId公司编号int4FalsePositionId职位编号int4FalseSalaryCalcId薪资计算逻辑主键uniqueidentifier16TrueCreateTime创建时间datetime8FalseModifyTime更新时间datetime8FalseCreator创建人nvarchar(50)100TrueModifier更新人nvarchar(50)100TrueState状态bit1FalseIndexesKeyNameColumnsUniquePK_Table_1_1SalaryCalcConfigIdTrueAuthor: AdministratorCopyright 2016 - All Rights ReservedPage 23 of 25Project (local) User databases QK365_Remuneration Tables dbo.HR_SalaryStatistics dbo.HR_SalaryStatisticsMS_Description员工月度薪资计算结果表PropertiesPropertyValueCollationChinese_PRC_CI_ASRow Count ()0Created17:46:33 2016年3月30日Last Modified11:00