January 21, 2012 at 2:58 am
Kindly provide with best meathod
.Database type: Health care
What i would like to do: instead of making tables like staff,employee,doctors etc i would like to make a generic person table provide a category (as above to person),then fill standard details common to all. After that would like to fill details specefic to particular person of particular category ( ie person who is in staff will have salary details)
Problem is how do i make table choosing person and category details
January 21, 2012 at 4:25 am
I think you should work on with normalization that help you to design your database.
http://www.sqlservercentral.com/articles/T-SQL/normalization/584/
January 21, 2012 at 2:17 pm
After that would like to fill details specific to particular person of particular category ( ie person who is in staff will have salary details)
A question for you. Why have salary details in a separate table?
1. Do you intend to make that table a history table, say for instance, a date,salary at that time.
2. Then if the individual is awarded a pay raise, do you want a second entry for that person with the new salary/pay rate and the date of the pay raise?
January 21, 2012 at 2:34 pm
There are a lot of ways to go about building a new database, and I think one of the first should be how its going to be used. Is this something that needs to be queried thousands if not millions of times a day? Or it is going to grab data, sort it, and infrequently be called. After you figure out what the purpose of the database system is, Normalization is probably the next thing to look at. There's a great article linked above with the basics of normalization. For something like a health care database, I'm guessing data integrity is fairly important so upping the amount of normalization and use of foreign keys might be beneficial here. I wrote up some mock code to give you an idea of the table structures, syntax and constraints that might be involved in your system. I made sure to include a situation where you have an employees table, and a table of possible employees role, which you link through an intermeidate table to get an employees role. The befit of architecture like that is you can (if you so desire it) have one employee with multiple roles in the company, and this does not break first normal form by having multiple columns in the employee table for "extra roles". I also showed a few joins at the bottom to aggregate the data from across the different tables.
begin try
create database mdm
end try
begin catch
end catch
go
use med
if not exists (select 1 from information_schema.tables where table_name = 'Employees')
begin
Create table Employees
(
EmployeeID int identity(1,1),
FIrstName varchar(100),
LastName varchar(100),
PhoneNumber varchar(15),
DateInserted datetime default getdate()
constraint PKC__Employee__EmployeeID primary key clustered (employeeid) on [PRIMARY]
)
end
if not exists (select 1 from information_schema.tables where table_name = 'RoleCategories')
begin
create table RoleCategories
(
RoleID int identity(1,1),
RoleTitle varchar(100) unique,
constraint PKC__RoleCategories__RoleID primary key clustered (RoleID) on [PRIMARY]
)
end
if not exists (select 1 from information_schema.tables where table_name = 'EmployeeRoles')
begin
create table EmployeeRoles
(
EmployeeID int,
RoleID int
constraint PKC__EmployeeRoles__EmployeeID_RoleID primary key clustered (EmployeeID, RoleID) on [PRIMARY]
constraint FK__EmployeeRoles__RoleCategories_RoleID_RoleID foreign key (roleID) references RoleCategories (roleID),
constraint FK__EmployeeRoles__RoleCategories_EmployeeID_EmployeeID foreign key (EmployeeID) references Employees (EmployeeID)
)
end
if not exists (select 1 from information_schema.tables where table_name = 'EmployeePay')
begin
create table EmployeePay
(
EmployeeID int,
StartDate int,
Payfloat,
per varchar(10)
constraint PKC____EmployeeID_StartDate primary key clustered (EmployeeID, StartDate) on [PRIMARY]
constraint FK__EmployeePay__Employees_EmployeeID_EmployeeID foreign key (EmployeeID) references Employees (EmployeeID)
)
end
insert into Employees (FIrstName, LastName, PhoneNumber)
select 'Tom', 'Sawyer', '123-456-7890'
if not exists (select 1 from med.dbo.RoleCategories where RoleTitle = 'Doctor')
insert into RoleCategories (RoleTitle)
select 'Doctor'
if not exists (select 1 from med.dbo.EmployeeRoles where EmployeeID = 1)
insert into EmployeeRoles
select 1, 1
if not exists (select 1 from EmployeePay where EmployeeID = 1 and StartDate = FLOOR(cast(getdate() as float) + 2))
insert into EmployeePay (EmployeeID, StartDate, Pay, Per)
select 1, FLOOR(cast(getdate() as float) + 2), '50000', 'Year'
select
e.FirstName,
e.LastName,
e.PhoneNumber,
er.RoleID,
rc.RoleTitle,
e.DateInserted,
CAST(ep.Pay as varchar(30)) + ' per ' + ep.per
from med.dbo.Employees e
inner join med.dbo.EmployeeRoles er
on e.EmployeeID = er.EmployeeID
inner join med.dbo.RoleCategories rc
on er.RoleID = rc.RoleID
inner join med.dbo.EmployeePay ep
on e.EmployeeID = ep.EmployeeID
where ep.StartDate = (select MAX(startdate)
from med.dbo.EmployeePay b
where ep.EmployeeID = b.EmployeeID
and b.StartDate <= ep.StartDate)
This sounds like an interesting undertaking and I wish you the best of luck with it. If I can elaborate on any of my code, or you 'd like to chat in more detail, please let me know and I'm happy to help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply