locked
One to Many Relation Ship [ Parent Child ] Database Design Help ? RRS feed

  • Question

  • Hi ,

    I have an Employee Master ,

    CREATE TABLE [dbo].[EmployeeInfo](
    	[ID] [int] NOT NULL,
    	[EmployeeName] [nvarchar](max) NOT NULL,
    	[Division] [nvarchar](50) NOT NULL,
    	[Role] [char](1) NULL,
    	[Supervisor] [int] NULL,
    	[Status] [nvarchar](2) NULL,
    	[supervisor1] [int] NULL,
     CONSTRAINT [PK_EmployeeInfo] PRIMARY KEY CLUSTERED 
    (
    	[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
    
    ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD FOREIGN KEY([supervisor1])
    REFERENCES [dbo].[EmployeeInfo] ([ID])
    GO
    
    ALTER TABLE [dbo].[EmployeeInfo] WITH CHECK ADD FOREIGN KEY([Supervisor])
    REFERENCES [dbo].[EmployeeInfo] ([ID])
    

    we are about to design an Intranet Web Application which records IN and OUT Timings for all employees .

    Emp_Timings Table .

    CREATE TABLE [dbo].[Emp_Timings](
    	[id] [int] NULL,
    	[Timing_type] [bit] NULL,
    	[Fixed_InTime] [datetime] NULL,
    	[Timing] [datetime] NULL,
    	[Att_Status] [bit] NULL
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[Emp_Timings] WITH CHECK ADD FOREIGN KEY([id])
    REFERENCES [dbo].[EmployeeInfo] ([ID])
    

    Any expert can please suggest me that i'm moving in a right path ?

    In Emp timings table Timing type bit would be either 1[In time],0[Out time].

    Fixed_intime would be a variable  entry in emp_Timings  .

    Timing column would be recorded with either INTime or OutTime for all the employees

    Att_Status records wheather an employee is present or absent on a specific day .

    IS that necessary to add a new column fixed_intime of type [ varchar/time ] to show what would be the fixed intime for individual employee ?

    Hope i'm clear .

    Please let me know if i'm doing anything wrong .

    Thanks in advance .

     


    Rajkumar Yelugu
    Wednesday, August 11, 2010 2:32 PM

Answers

  • Hi Raj,

             Looks good .. these are the observations:

     

    1. Is it necessary to have MAX for the column "Employee name" ---> 
         [EmployeeName] [nvarchar](max) .
      I think, data length can be reduced based on the requirement.

    2. I think, it is better to maintain the fixed timings in separate table say 
    Emp_Schedule_Timing. Here you can maintain the change history of the employee 
    schedule(fixed) timings. The table structure can be:

    Seq_ID , Employee_Id , Fixed_In_Time , Fixed_Out_Time , Start_Date , End_Date

    In this table, data will be appended whenever the fixed timing changes. Here we can 
    have complete track of the changes.

    3. Also, if you are sure that if we are capturing only IN and OUT timings in a day .. then we 
    can have both of them as columns in Emp_Timings table. This will reduce the number of 
    records.

    -- Reddy Balaji C.

     

    • Proposed as answer by Naomi NEditor Thursday, August 12, 2010 3:31 PM
    • Marked as answer by Rajkumar Yelugu Wednesday, August 18, 2010 11:49 AM
    Wednesday, August 11, 2010 4:18 PM

All replies

  • IS it necessary to add a new column fixed_intime of type [ varchar/time ] to show what would be the fixed intime for individual employee for the first entry into emp_timings .

    I just thought of adding a column of type varchar fixed intime to empMaster then show it in the front end application for the first entry in time  and later on just thought of firing a trigger from emp_timings table to change the fixed intime if in any case it have to be manipulated from the front end form .

    For each entity i need to record a fixed_intime  value and it can be modified at any point of time during the application flow .

    Thanks .


    Rajkumar Yelugu
    Wednesday, August 11, 2010 2:43 PM
  • Hi Raj,

             Looks good .. these are the observations:

     

    1. Is it necessary to have MAX for the column "Employee name" ---> 
         [EmployeeName] [nvarchar](max) .
      I think, data length can be reduced based on the requirement.

    2. I think, it is better to maintain the fixed timings in separate table say 
    Emp_Schedule_Timing. Here you can maintain the change history of the employee 
    schedule(fixed) timings. The table structure can be:

    Seq_ID , Employee_Id , Fixed_In_Time , Fixed_Out_Time , Start_Date , End_Date

    In this table, data will be appended whenever the fixed timing changes. Here we can 
    have complete track of the changes.

    3. Also, if you are sure that if we are capturing only IN and OUT timings in a day .. then we 
    can have both of them as columns in Emp_Timings table. This will reduce the number of 
    records.

    -- Reddy Balaji C.

     

    • Proposed as answer by Naomi NEditor Thursday, August 12, 2010 3:31 PM
    • Marked as answer by Rajkumar Yelugu Wednesday, August 18, 2010 11:49 AM
    Wednesday, August 11, 2010 4:18 PM
  • Reddy Balaji  ,

    Thanks for your inputs will work this out as suggested and update the post accordingly .

    Thanks.


    Rajkumar Yelugu
    Thursday, August 12, 2010 2:55 PM
  • hi,

     Thanks for the many relationship parent child database design help. it will help us and others in a same way. can you help us in a different database design ?

     

     

     

    ------------------------------------------------------------------------------------------------------------------

    Want to get-on Google's first page and loads of traffic to your website? Hire a SEO Specialist from Ocean Groups  [url=http://oceangroups.org/] seo pecialist [/url]

    Wednesday, August 18, 2010 11:03 AM