tsql - Database Design SQL -
my question is: notice putting mvrid in many tables. opinion of following desing below. how can improve it? if point me book hope page of book.
can execute script on database called medicalvariance if want see whole thing.
use medicalvariance; --this quick install script --i guess execute front end overkill --because of audience install software dba's if exists ( --the query below will evaluate true if finds foreign key constraint. select 1 information_schema.table_constraints constraint_type 'foreign key' ) begin declare @tablename nvarchar(100) declare @constraintname nvarchar(100) declare @dynamicsqlexec nvarchar(300) --declare , feed cursor data declare constraintcursor cursor fast_forward -- dont worry wont drop precious foreign keys since catalog must medicalvariance select table_name,constraint_name information_schema.table_constraints constraint_type 'foreign key' , constraint_catalog 'medicalvariance' --open cursor open constraintcursor fetch next constraintcursor @tablename, @constraintname --now implement logic drop constraints while @@fetch_status =0 begin --dynamic sql pain if think got better syntax go set @dynamicsqlexec ='alter table '-- space important set @dynamicsqlexec =@dynamicsqlexec + @tablename + ' ' set @dynamicsqlexec =@dynamicsqlexec + 'drop constraint ' set @dynamicsqlexec =@dynamicsqlexec + @constraintname print @dynamicsqlexec -- make sure correct sql syntax exec(@dynamicsqlexec) -----------------------dynamic sql ends------------------------------------- fetch next constraintcursor @tablename, @constraintname end; close constraintcursor deallocate constraintcursor end; go if exists ( select 1 information_schema.tables table_name = 'mvrmeds' ) begin drop table mvrmeds end; go if exists ( select 1 information_schema.tables table_name = 'mvr' ) begin drop table dbo.mvr end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsadminroute' ) begin drop table dbo.mvrmedsadminroute end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsprescribingerrors' ) begin drop table dbo.mvrmedsprescribingerrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedstranscribingerrors' ) begin drop table dbo.mvrmedstranscribingerrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsproductissueserrors' ) begin drop table dbo.mvrmedsproductissueserrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsprocumenterrors' ) begin drop table dbo.mvrmedsprocumenterrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsdispensingerrors' ) begin drop table dbo.mvrmedsdispensingerrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsadministrationerrors' ) begin drop table dbo.mvrmedsadministrationerrors end; go if exists ( select 1 information_schema.tables table_name = 'mvrmedsdocumentationerrors' ) begin drop table dbo.mvrmedsdocumentationerrors end; go if exists ( select 1 information_schema.tables table_name = 'mvremployees' ) begin drop table dbo.mvremployees end; go if exists ( select 1 information_schema.tables table_name = 'mvrcommunicationerrors' ) begin drop table dbo.mvrcommunicationerrors end; go -- way putting mvrid in every table -- recommend or bless desing? create table mvr ( mvrid int not null primary key ) create table mvrmedsadminroute ( mvrmedsadminrouteid int not null primary key, mvrid int ) create table mvrmeds ( mvrmedsid int not null primary key, mvrid int , mvrmedsadminrouteid int , constraint mvrmeds_mvr_fk foreign key(mvrid) references dbo.mvr(mvrid), constraint mvrmeds_mvrmedsadminroute_fk foreign key (mvrmedsadminrouteid) references dbo.mvrmedsadminroute(mvrmedsadminrouteid) ) create table mvrmedsprescribingerrors ( mvrprescribingerrorid int not null primary key, mvrmedsid int , mvrid int constraint mvrmedsprescribingerrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedstranscribingerrors ( mvrtranscribingerrorsid int not null primary key, mvrmedsid int , mvrid int constraint mvrmedstranscribingerrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedsproductissueserrors ( mvrtranscribingerrorsid int not null primary key, mvrmedsid int , mvrid int constraint mvrmedsproductissueserrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedsprocumenterrors ( mvrprocumenterrorsid int not null primary key, mvrmedsid int, mvrid int constraint mvrmedsorderingprocumenterrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedsdispensingerrors ( mvrdispensingerrorsid int not null primary key, mvrmedsid int, mvrid int constraint mvrmedsdispensingerrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedsadministrationerrors ( mvradministrationerrorsid int not null primary key, mvrmedsid int, mvrid int constraint mvrmedsadministrationerrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) create table mvrmedsdocumentationerrors ( mvrdocumentationerrorsid int not null primary key, mvrmedsid int, mvrid int constraint mvrmedsdocumentationerrors_mvrmeds_fk foreign key (mvrmedsid) references dbo.mvrmeds(mvrmedsid) ) ----employees --only employees can part of mvr? create table mvremployees ( mvremployeesid int primary key, mvrid int, constraint mvremployees_mvr_fk foreign key (mvrid) references dbo.mvr(mvrid) ) create table mvrcommunicationerrors ( mvrcommunicationerrorsid int not null primary key, mvremployeesid int, mvrid int, constraint mvrcommunicationerrors_mvremployees_fk foreign key (mvremployeesid) references dbo.mvremployees(mvremployeesid) )
if mvrid in tables come primary key mvrid in table mvr should have foreign key constraint on mvrid columns. @ least if want have control on mvrid's used in other tables.
you have not specified why added mvrid table , depending on how want values used can necessary or can bad idea.
it necessary if adds information entity stored unnecessary if information retrieved using relationship table.
ex: mvrmedsprescribingerrors child table mvrmeds fk mvrmedsid. if can use mvrmedsid in mvrmedsprescribingerrors find associated mvrid in mvrmeds not not store mvrid in mvrmedsprescribingerrors.
but if on other hand can store mvrid's in mvrmedsprescribingerrors not same mvrid on related mvrmeds row absolutely necessary have mvrid in mvrmedsprescribingerrors
Comments
Post a Comment