数据库设计

数据库设计与 PowerDesigner 建表

数据库的设计决定了系统的稳定性、可扩展性和性能等。

PowerDesigner 表设计要点

• precision：精确度
• M（Mandatory）：强制的，即非空约束
• 主外键关系：外键所在的表是子表，外键对应的主键所在的表是主表。要为某一张表添加外键，应点击 Reference，然后从子表开始连线，箭头指向父表。也就是哪张表要创建外键，就先在这张表添加一个字段，然后从这张表开始连线连接它的父表。

自动生成的建表脚本示例

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Leave') and o.name = 'FK_LEAVE_REFERENCE_ACCOUNT')
alter table Leave
   drop constraint FK_LEAVE_REFERENCE_ACCOUNT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Account')
            and   type = 'U')
   drop table Account
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Leave')
            and   type = 'U')
   drop table Leave
go

/*==============================================================*/
/* Table: Account                                               */
/*==============================================================*/
create table Account (
   ID                   bigint               identity,
   Name                 nvarchar(25)         not null,
   Password             nvarchar(255)        not null,
   RegisterTime         datetime             null,
   Gender               bit                  null,
   constraint PK_ACCOUNT primary key (ID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户表',
   'user', @CurrentUser, 'table', 'Account'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '主键ID',
   'user', @CurrentUser, 'table', 'Account', 'column', 'ID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户名',
   'user', @CurrentUser, 'table', 'Account', 'column', 'Name'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '密码',
   'user', @CurrentUser, 'table', 'Account', 'column', 'Password'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '注册时间',
   'user', @CurrentUser, 'table', 'Account', 'column', 'RegisterTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '性别',
   'user', @CurrentUser, 'table', 'Account', 'column', 'Gender'
go

/*==============================================================*/
/* Table: Leave                                                 */
/*==============================================================*/
create table Leave (
   ID                   bigint               not null,
   AccountID            bigint               null,
   BeginTime            datetime             null,
   EndTime              datetime             null,
   Hours                float                null,
   AddTime              datetime             null,
   Status               bit                  null,
   constraint PK_LEAVE primary key (ID)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '请假表',
   'user', @CurrentUser, 'table', 'Leave'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '主键ID',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'ID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '用户ID',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'AccountID'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '开始时间',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'BeginTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '结束时间',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'EndTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '小时数',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'Hours'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '添加时间',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'AddTime'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '状态',
   'user', @CurrentUser, 'table', 'Leave', 'column', 'Status'
go

alter table Leave
   add constraint FK_LEAVE_REFERENCE_ACCOUNT foreign key (AccountID)
      references Account (ID)
goCode language: PHP (php)