General Database Design Question


Okay, I have my idea of how to do this, but would like to get
experience's idea of how to do this since I am a mere 3.5 weeks into
using SQL.

I am working on a time off tracker for my company. We have an employee table (employee), a request table(request). Originally I was told that all vacation is a lump of days and they take time off and it is subtracted from their vacation time. Now, I am getting told they can take off vacation, pto, bereavement and possibly more.

I have a time off table now (time_off) that I made just so I could add time off types for tracking what time of time off the employee took based on having one pool of time off. I was thinking I needed to add another table (tracking) that shows the employee id as a foriegn key to the employee table then have the table show time off type, accrual rate (cause some accrue at 2 weeks per year and others at 3 days per year) and a column for increments cause some they can take in increments of 15 minutes and others half days.

My thought was when I create a new employee, I have put in what types of time off they are allowed, what the accrual rates for those are. When they take a day off, put it in the request field and then when they want to view their available time off, say for the employee, how many vacation days have they accrued from the tracking database, minus the days they have taken off, leaves the number of days they have available.

I would LOVE and APPRECIATE your input, cause I don't want to go down this path only to find out I could have / should have done it differently.

One of the things I haven't really figured out is if they add a new time off type, how to add it for all the employees and make sure it is accounted for.
0
dprichard
10/6/2006 7:43:25 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
627 Views

Similar Articles

[PageSpeed] 5

Employee time is very complex and complicated because you have to account for Holidays, you need a Calendar table and how to take care of the Holidays plus regular time off.  The links below will take you in the right direction, it will help if you are using Time Tracker started kit, if your are not get the white paper.  Hope this helps.

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

http://www.smart.net/~mmontes/ushols.html#ALG

http://www.asp.net/downloads/default11.aspx?tabid=62


Kind regards,
Gift Peddie
0
Caddre
10/6/2006 8:58:20 PM

Sorry I did not give you the calendar link here it is.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


Kind regards,
Gift Peddie
0
Caddre
10/6/2006 11:49:20 PM
Reply:

Similar Artilces:

GENERAL SQL SERVER: Question about the Script that setups the SQL Server Database.
This is the first time I have created a script for Sql Server 2000, and I have run into a few syntax ereas that I am not familar with. Bellow is a section of the script from the Portal server packages. At the end of the sample, is "ON [PRIMARY], Can anyone explain to me what this does and why the primary key isnt setup inside the code itself? I am basically trying to take parts of the script and incorrporate it into my own script and hate added syntax that I dont understand, makes debugging a real pain in the future. CREATE TABLE [dbo].[Portal_Announcements] ( [ItemID] [in...

SQL Database Design Question
Hi, I'm relatively new to SQL, and im just after a few ideas on the best approach to designing databases for a project i am currently working on.  It is a financial application to accumulate debit and credit counts and totals entered into a web form (using DataLists). The problem i am having is how i should setup this database.  I have approx 5 different types of this data to collect, but all collecting the exact same details, so at the moment I have 5 duplicate tables named differently for each of these 5 types to collect the counts and totals.  I'm not sure if this is...

Design & Database Design Question
Hi I build computers and currently we use a access database to keep track of each system. Each system is generated a serial no and I input the different components and their serial numbers, then print out a system build summary sheet to go with the finished pc. We have two stores that build the computers.  Each store has a seperate database. Each store has unique system serial numbers such as Store1: DLX07-IN3145 Store2: DLX-DR2156 What I am wanting to do is create a web-based version in asp.net vb with SQL 2005 DB backend. And have both sto...

Backing up the sql database(*.bak) and restoring the Database in VB.net
Hi There, I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me. Thanks in advan...

Database Design Question
In our system we have several modules which are "optional" for purchase. In the past we've kept the table definitions for them in our main database. This has worked ok except in the case where 2 customers have different table definitions for the same tables because of a different business model. What we are considering is keeping the optional tables or tables that are customer specific in a different database and accessing them as proxy tables via the main database. This would allow us more plug and play type of access. The databases would reside on the same s...

general Sql question
Given the following tables Table1 --------- labnumber name doctor Table 2 --------- labnumber ailmentcode (hold 0 to many ailments for records in table 2) How can I get all the records in table 1 that have ailments (related in table 2 by labnumber) 1, 2, 3, 4, 5, 6, 7, 8, and 9. Thanks in advance Blake select table1.* from table1, table2 where table1.labnumber = table2.labnumber and table2.ailment in (1,2,3,4,5,6,7,8,9) Instead of the IN clause you could code and table2.ailment between 1 and 9 or even and table2.ailment >= 1 and table2.ailme...

A database design question.
I would like to throw a question out to you guys. We have three concepts that Management thinks of as three databases. Users, clients, products. Users register to use products that we develop for clients. They all interact with each other and have constraints across the concepts. One idea thrown out was.. 'Hey why don't we have three databases?? There is too much going on for it to be one database.' Personally it seems like one system to me. Thus one database. What is the opinion out there about having three databases continually interacting with each other. What kind...

Database design question
Not strictly a PB problem, but I never get any help from the SQLServer newsgroups... We have an Items table, which contains master data about items. This data is fairly static. We need to store stock level information (quantity in stock, on order, allocated to customers, etc) for each item. We can either do this by adding columns to the Items table, or creating a new table, with a one-to-one relationship with Items. I can think of the following pros and cons: 1. Adding columns to Items. Queries will only have to select from a single table. Database is normalised. 2. New table ...

Database design question
Suppose I have the following tables: Customers Warehouses Employees Each of these can have an address. My question is, what's the best way of representing this? I can think of two approaches: 1) Have 3 address tables: Customers ----> CustomerAddresses Warehouses ---> WarehouseAddresses Employees ----> EmployeeAddresses 2) Have 1 address table with three join tables: Customers ----> Customer_x_Addresses ---------\ Warehouses ---> Warehouse_x_Addresses ---------> Addresses Employees ----> Employe...

General SQL Question
 I am trying to create a query that will return the matching image name from one table or displays a null value for that column if there is no matching row. So for example I want to go through all the event table rows and match up the row information with image_name from Image Gallery table. If nothing matches up from that row I want a null value displayed. I been working on this for a while and all I manage to come up with is only returning rows that have matching IDs from Image Gallery and Event.  --------Image Gallery----------Image Name |  EventIDBaseball  &...

general SQL question
--------------7EC95B4FEE051BD577C3D038 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a general SQL question. If there is a two-column table, e.g., id keyword -- ------ 1 k1 2 k2 1 k2 2 k3 (1) Is it possible to retrieve the values for id 1 by saying "select id from foo where keyword=k1 and keyword=k2" The above returns nothing. Since we are comparing against a single column (keyword), this doesn't seem possible to do the "and". (2) If...

SQL database question
Hi, Since there is no user interface for creating sql server databases with VWD, can I create databases with Visual studio 1.* and use them with VWD? What about the compatibility and constraints? Otherwise, if I want to create databases programmatically, is the code in VWD different from visual studio 1.*? Is there somewhere where I can find samples? Thanks for any pointer. Char22 You could build a sql script based on modifing the sqlTour or Geekspeak sql scripts and execute a Run command like ex. in the Tour or Geekspeak samples. ...

SQL database question
I have a small question and I`d appreciate an answer. I`m still learning asp.net and I`m having some trouble with my database. I want to access the VideoPath and VideoName from my database, to pass on to my flv player embedded in my page. Can anyone explain exactly how I can do this? Thanks in advance!  Go through very easy and simple to understand ado.net tutorial http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspxKamran ShahidSr. Software Engineer(MCP,MCAD.net,MCSD.net,MCTS,MCPD.net[web])Netprosys Inc.www.netprosys.comRemember to click "Mark as Answer" on the p...

Web resources about - General Database Design Question - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

Resources last updated: 12/6/2015 9:10:05 PM