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.
HeyI need to store something a little different in a DB and I was hoping one ofyou guys might be able to help me.Basically it represents a 'world'. I have an initial state and then I getinfo like this...27/11/03 17:21 Mary is born27/11/03 17:21 Dave is born27/11/03 17:22 Sean is born27/11/03 17:23 Peter dies27/11/03 17:23 Fred is bornI need to be able to run querys like this...How many people are alive at 27/11/03 17:22Who was born between 27/11/03 17:22 and 27/11/03 17:23etc.Problem is, I'm going to have hundres of 'world's each with thousands ofentrys.All help is appreciated :)TnxNaomi
My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases. I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.Is he all wet, or would there be gains to be made?TIA
guys i have a programming question i have table called prerequsiteswhich is like thisprerequisite component componenta bso inside my before insert trigger i have to check if the prerequisite is a valid entry like for instance an entry like b is a prerequisite component for component a which is not a valid entry since i already have an entry in the prerequisite table which defines that comonent a is a prerequisite of b. however this is very basic so i can test it easy some thing like this in my before insert prerequisite component componenta bb a ( Not a valid entry)select count(*) from prerequisite where component = value of prerequisite component and prerequisite component = value of component the above select statement will actually tell me if entry is allowed for insert and does not override other prerequsitesbut it can get very complex depending on how the user chooses so if someone can tell me a nice algorithm or good logic to approach this problem it would be great like for instanceprerequisite component componenta bb cc a ( Not a valid entry. so how do i ensure that this entry is not allowed considering that complexity for prerequsites can increase quite greatly)please can someone tell me how to approach this problem.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
Hi All, I am designing database where few of the master tables will reside in different database or in case different server. Scenario is Server "A" with Database "A" may host the "Accounts" table. Server "B" with Database "B" may host the "Product" table. I am designing database "Project" which will hosted in Server "A". My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables. How do i design my database and sql queries? I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
Hello, I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..andwhat sort of service or products they can supply. For instance a Employment agency maybe under professional services Table 1 Customer Customer_ID = primary key,,,, Sector_ID = Foreign keyComapany Name, Address, Phone, Postcode etcTabel 2 Industry SectorsSector_ID = primary key,,,,Customer_ID= foreign key banking, Education,Prof Services, etc Table 3 Trading ActivityTrading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= FkEmployment Agent, School, Lawyer etcTable 4 ProductsProducts_ID = primary key,,,,Trading_ID = foreign keySupply frozen foods, transport services, sports goods, etc Table 5 Account Account_ID = primary key,,,,Customer_ID = foreign keyAccount Name, Credit Limit, Payment Terms, Open date, Account contact etc One big point of confusion is, can I have the Customer_ID from the principal Customers tablein every table as a foreign key or must the tables be chained together one after the other as such. Advice appreciatedThanks
Hi, I need a hand with designing a database. I am collecting results from a survey which has the following questions: Call ref? How did you place your support call? Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed? 1 = very satisfied and 10 = very unsatisfied. How happy were you with the customer service you received upon placing the support call? 1 = very unhappy and 10 = very happy.How satisfied were you with the amount of time you had to wait until you heard from an engineer? 1 = very satisfied and 10 = very unsatisfied. How satisfied were you with the time taken to get your problem/query resolved? 1 = very satisfied and 10 = very unsatisfied Did you feel the engineer had enough knowledge to deal with your call? 1 = very good and 10 = not very good Overall how satisfied were you with the support call placed? 1 = very satisfied and 10 = very unsatisfiedIs there anything we can do to improve the quality of the support and service you received? I want to store this in a database. Obviously I want to use best practice for design, normalisation etc. The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database. Any help appreciated! Thanks Andrew
I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided. My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments. What is best practice for putting this db structure together? Thanks Andrew
Can anyone tell me how can i design database architecture for the Table Category & Product...so that i can make N-level entity relation....I have database in SQL SERVER 2000.
I guess I am confused about something and need some help. I am looking at a database schema for about 20 tables in a database. I noticed that the firstcolumn in each table Is some type of Id. For example StudentId,TestId etc.Where the Id is a unique numeric sequential value. So I have some questions?1- Do these Id's act as what are called indexes for the table?(Unique indexes)2- If the answer to 1 is correct, then how do I create these unique indexes? Is it as simple as declaring the Id column as the primary key and that this value will be generated automatically upon insert? 3- Is it necessary to have an Id column for every table, or may I only do it for a few of them?4. In relation to Question2, what do I need to do, so that the Id column will automatically be created when someone inserts a value into the database table with an ID column. For example here are two sample tables StudentsInfo StudentId<PK> Name Age 1 Mark 33 2 Jill 23 3 Mary 25 PersonalInfo Name<FK> SocialSecurity Address MajorMark 324-444-3342 15 Elm ArtJill 888-888-8998 21 North ScienceMary 876-777-2344 18 Byle Music
Hi,I am planning to create a technical forum for our college. could any one give me an idea of how to design the database for the forum. what table and columns i should have?
I have got a design issue.I have got 4 tables,having relationships.Now,user will edit records and save them,but I want to save the values which were exixsting before they were edited and saved.Implying,if a record is edited 50 times,then the values of each edit needs to be saved.How best ,can I achieve this.
Hi !!We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)2) We have a Tables called : Interest & Customer_InterestCustomer Table:CustomerID, Customer Name, Address, Email, Signup DateInterest Table:InterestID, InterestNameCustomer_Interest: (Need suggestion for How to design this)Should Table be design like:Option1: CustomerID, InterestIDOption2: CustomerID, Interest1, Interest2, Interest3, Interest4i.e.Lets Say: Customer table has CustomerA, CustomerB, CustomerCInterest table has Interest I1, I2, I3, I4Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4As per Option1:Customer_Interest Table witll haveCustomerA, I1CustomerA, I2CustomerA, I3CustomerB, I1CustomerB, I4ORAs per Option2Customer_Interest (Where Interest Column is bit column..... 1 = Signed up, 0 = Not Signed upCustomerA, 1, 1, 1, 0CustomerB, 1, 0, 0, 1Which way we should design? 3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?Thanks !!!
I am designing an inventory database in which I need some help, I have the following entities:ItemsNotebookWireless CardADSL Modemetc...ModelsAcer centrino 1.6Acer centrino 1.733COM 4x125 hours Wi-fi access50 hours Wi-fi accessetc... PackagesPackage A:Acer centrino 1.63COM 4x125 hours Wi-fi accessPackage B:Acer centrino 1.733COM 4x150 hours Wi-fi accessI made a table for the items having the following fields: Item_ID, Item_Nameand another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)up to this point is this correct?About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes themI don't know how to relate the packages and the models table.Any recommendations for a proper design for those entities?
I am creating a knowledge base. I want the user to be able to choose a category such as Hardware, Software, Etc. then I want them to be able to choose what type of software such as word, excel, ets. It will then display in a gridview problems, solutions, submitted by, last updated, and review date. The gridview will allow users to update solutions and I would also like the ability for technicians to be able to add new Problems/solutions.
I have requirement to create table of 100 columns. Should i create 100 columns in one table or i should create three coulmn table with column id, column desc, column value with 1 row in 100 column table = 100 row in 3 column table. Which will be faster & efficient in performance, io & size.
The database will provide content management for an Intranet site.
On the site we have a number of articles which are categorised. Each categories can also have sub-categories.
The relationships would seem to be
Category---->Sub-Category----->Article
A category has one or mant sub-categories. A sub-category has one or many articles.
However some categories have articles but are no sub-categories.
Should I create a further relationship between Category and Article to cater for this instance or should their be a dummy category record in this instance?
I am designing a sql server database to keep track of patient history and data about various diseases. Is there a book or any kind of resource that I can refer? Any help is appreciated.
I had a consultant design a database for me and every table contains a GUID. Most tables already contain a field that is unique. Is this necessary? In my view, the queries are no more efficient and it just takes up more space.
trying to work on a new project, i need some ideas to setup this design model. sql 2000 database and asp. This is call center environment where agent bid on shift (schedules) base on most disireable to least, and it process base on seniority.
Hi I was wondering if you could help me. I am currently trying to design a database. This database 4 tables, Main_Cat, Sub_Cat,SubSub_Cat and Prod_parent. How ever, not every sub_cat has a subsub_cat. And waas just wondering how I would link this.
Thanks
Ashley
I have attached an image of what I have so far, if any one could help it would be greatly appreciated
hi Actually i have been assigned a project in Sql server 2000 with front end in vb 6, and its an inventory control system of a computer retail company. i dont know how to make its design. it must show inventory items and new purchases and when they are made they must update the inventory and when the items in the inventory are taken to prepare orders recieved from the customers the inventory must be updated. it must also show customers back orders, daily expenditurers, monthly expenditures, staff handling the inventory, different brands ND JOURNAL AND LEDGER Accounts, journal accounts show daily expenses of a month and ledger shows monthly expenses and it must also show yearly expenses and net profit and the transactions made to the company accounts by the customers and when items are bought from the supplier it must automatically update the company's account when the comapny makes payment to the supplier. Thanks...........
I need help to design a database. The Scenario is:
There are 5 Departments
Dept1 Dept2 Dept3 Dept4 Dept5
Each department has 10 batches of students. The questions are stored in SQL Server 2000 Database. Each batch has 8 subjects. The question for each batch are 200-400. Some questions has 4 options and some has 7 options to select multiple aswers. Some questions as more than 1 answers(Ans1, Ans2, Ans3). Some Questions also has 2-3 pictures. During test a student can select Check option to review its answer again. Database should be capable to hold 5000 users at a time.
My questions are:
How should I design database; should I design Department wise or batch wise or Subject wise? And does database design will be different for a Web based application and Windows based application? And Is Sql Server my correct choice?
I've got a table of computers and each computer is assigned to a computer group.
I also have a table of content and a content item is assigned to multiple computer groups.
This gives the following structure:
Computer --------- ComputerID int pk GroupID int fk
ComputerGroup --------------- GroupID pk
ComputerGroupContent -------------------- GroupID fk ContentID fk
Content ------- ContentID pk
Now, this is the bit I'm not so sure about....
Each content item can have a number of attributes that can be customized by a computer. So for example, a piece of content has an attribue called 'Title' that each computer can provide a value for it
Thus here are the additional tables:
ContentAttribute ---------------- AttributeID int pk ContentID int fk Name string
AttributeValues --------------- ComputerID int fk AttributeID fk Value string
So the issue I have here is that there's nothing to stop a computer from having content attribute values for a content row that is not actually assigned to the computer's group!
