we have records with login.out of employees.
i.e.
this is a set of login/out for an employee for 1 day (but the actual data shows 2 dates )
login 1/1/2005 15:00
logout 1/1/2005 16:00
login 1/1/2005 17:00
logout 1/1/2005 18:00
login 1/1/2005 23:00
logout 1/2/2005 1:00
login 1/2/2005 2:00 <-----------this is the reason why we need a new table called schedules
logout 1/2/2005 3:00 some employee goes over the next day to finish their shift
I think in order to get the proper Start Time and Stop time we need a new table called schedules.
schedules will contain employee work schedules, kindly help me design how to accomplish this.
question is, will it be for each day?
so I'll have 365 record per employee?
help me design the columns
employeeID, startTime,stopTime, workdate -do I need only four?
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
When I set the relationship between two tables with a one-to-many relationship and I want all records deleted from the many side when a row is deleted from the one side, how should I set the Insert and Update Specs (Delete and Update) CASCADE or NO ACTION? Then if there is a lookup table on the many side how should it be set?
i am a beginner and any response will help me a lot. i have 20 types of products,each product has about 40 fields, most of them are common fileds for all products, but some of them are unique. the number of the unique fields of all the product types is 75. the question:Should i build one products table with 75 fields and leave nulls where a field is not property of a product type?Or should i build a products table for the 30 common fields and a 20 different tables for each product type extra fields?Or just build 20 different tables each product type? other ideas are welcome
I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback. Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution. Here is the table design I have so far http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list... Thoughts please. TIA
Amanda writes "I'm a student currently taking a class working with the SQL database and I'm not doing too well in that class because I'm struggling on some basics. I have worksheets and everything i just need help working on them, and this being and on-line class makes it hard for me to work with my teacher. If someone is willing to work with me one on one through a messaging program or Email that would be great, i can send you the worksheets and we can work it out together."
Hi,Probably a very simple question but I'm afraid my experience at this issomewhat limited so...In a database with a many to one relationship, say orders to customers, isit preferable to have an UID for a customer and store this in the orderstable rather than the customer name, for example.CUSTOMERS++++++++++UID: Name: Address: Postcode: etc01 Alpha 12 Acacia Ave HN7YHH02 Beta 23 Acacia Ave HN6YTH03 Gamma 28 Acacia Ave HN7UYHORDERSCustomer Amount dateAlpha 100 20030823Alpha 250 20030824Beta 90 20030825Alpha 800 20030825Gamma 1000 20030826Or is it is better to have:ORDERSCustomer Amount date01 100 2003082301 250 2003082402 90 2003082501 800 2003082503 1000 20030826Assuming that the second option is the best, how far do I go? I can see howit would beof benifit to do this for orders and customers where there may beseveral hundred thousand orders and hundreds of customers, but for examplewould I need to do this for a customer contact database with titles - egeach title having a UID and using this ID in the customer table?Thanks for taking the time to read this. Any advice is gratefully received.Kind thanksChris S
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name : Nestle milk | Rainbow milk packages : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos | CTN=8 Nos
A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G
I want to run that data through some substrings etc. and dump it into another table like this:
//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM //EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS. CREATE TABLE ISIS_DATA_PREP (ISIS_STATUS_ID VARCHAR(15), STATUS VARCHAR(5), STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY, LAST_NAME VARCHAR(25), FIRST_NAME VARCHAR(20), MID_INIT CHAR(1), CLASS NUMERIC(2), EXPIR_DATE VARCHAR(10), SEX CHAR(1), BIRTHDAY VARCHAR(10), CAT NUMERIC(3), SCHOOL VARCHAR(5))
Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?
So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.
Now I get this message:
Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated.
I look up this error on MS's knowledge base and get something along these lines:
...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....
QUESTION #2 -
What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.
QUESTION #3 - My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.
Sorry for the absolutely massive post. : ) And thanks for any feedback.
I have a real basic table index question. If I have a clustered index as follows:
CREATE CLUSTERED INDEX [IX_HF1] ON [dbo].[Table1]([Column1],[Column2]) ON [PRIMARY]
and I am querying just on Column2, would it be more efficient and faster, in terms of the query, for me to also have a nonclustered index with just Column2 (as in example below) ?
CREATE INDEX [IX_HF2] ON [dbo].[Table1]([Column2]) ON [PRIMARY]
I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.
In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.
In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.
An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.
If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.
I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database Dim connectionToAccess As New OleDbConnection(DBConnectionAccString) strsql = "SELECT * FROM [TABELLA]" connectionToAccess.Open() Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS") DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella") DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database Dim connectionToSQL As New SqlConnection(DBConnectionSQLString) connectionToSQL.Open() Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL") DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella") DaSQL.Fill(DsAccess, "Tabella")
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain table is JobMaterial. It has materialid, jobid, supplierid, description and TypeFactor(which represents the type of order) means that the material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow can I design this table. If I take, ColorOrSize column, it will refer different values for diffrent typefactor. When by size, it will have Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance
I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders? Thanks.
Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.
I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table? Or how can I redesign this table.
I'm currently developing a real estate system to manage order processing and work flow. I'm a little uncertain as to how to design the tables because an order can have N number of applicants, owners, buyers, and properties. There are cases where there are 9 different buyers and some where the number of properties exceeds 20. It seems that normalization might make the situation crazy, but I'm a touch rusty. Thanks.
I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:
Item | Quantity | Unit Price | SubTotal | Total
Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc... how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...
If my question is not clear, let me know, I will try to clarify.
Hi am trying to figure out my table design need some advice
I have a table that catches the telephone numbers of a customer , there are 5 telephones for each customer. All the numbers are there in the DB
Should I create the design in the following format
CustomerName Tel1 Tel2 Tel3 Tel4 Tel5
Or Should I have
Customer Telephone
& then have 5 rows for each customer
Please advice
I even wanted to knw how should the design be , if all the Tel Nos are not captured, I mean what if some customers have 3 tel Nos, while some may just have 1 while some may have all 5
Hi I'm having trouble figuring out this database design and normalising the data. Following are the tables and I am wonder if it can be normalised any further. This is a database of Architects and their work history and building they have designed.
Architects: FirstName LastName Gender DOB Bio
Works: Title Client Type_of_Building Style Address Year_Designed Year_Completed Comments Architect_ID
Firm: (this is the various firms that the architect has worked for) CompanyName Address
EmploymentHistory: (when the architect worked with a particular firm) Year_Start Year_End Firm_ID Architect_ID
Publication: (where the architects works were published) Author Date_Publication Title Source Publisher Architect_ID
I would really appreciate some help and suggestions on creating the relationships. thanks
employee table ------------------------ EMP_ID varchar NAME varchar DEPARTMENT_CODEint POSITION_CODEint COUNTRY_CODEint
department table ------------------ DEPARTMENT_CODEint DEPARTMENT_NAMEvarchar
position table --------------- POSITION_CODEint POSITION_NAMEvarchar
country table ------------- COUNTRY_CODEint COUNTRY_NAMEvarchar
In employee table I have to store two country names. ie. employee currently working in this country but his home country name (origin country) is another name. In country table all countries information available. ie. country_code and country_name.
How I can design the employee table that it get country_name from COUNTRY TABLE ? but we can not use same country_code in two fields.
OR otherwise I will have to create employee table like this
I'm struggling coming up with an optimal table design for the following scenario.
I have a table in which each row is recording information about a document - author, publisher, publication, title, abstract and so forth. I also have an image table which contains information about the images which can be associated with the documents.
Each document can have on of three image types associated with it - an author photo, a publisher logo and a publication logo. There aren't actually that many different authors, publishers and publications in the database so it would be common for many documents to have the same author or the same publisher although authors sometimes write for different publications. In the current setup it's possible for a document to have more than one image of each type associated with it, which is undesirable - this needs to eliminated and we need to ensure that each document can only have one image of each type associated with it.
It'd also be a huge boon if I could get all the information about a document and it's attached images with a single database call. In the current setup, trying to do this returns multiple rows so I've ended up making two calls, one for the document and one for it's attached images.
I can see two possible ways of setting this up, neither of which appeal.
The first is to add three columns to my document table to record the image URLs which is simple but inflexible should I want to add more image types later on.
The second is to set up three new tables for author, publisher and publication and associate each of these with the document table through a key and then to the image table through another key. There's also an image type table which is going to come in to play. I'm unwilling to do this because it's going to just explode the number of tables I've got, especially if we add more image types, and play havoc with my XML schemas.
Is there another way of rationalizing this that I've missed?
I am trying to decide between 2 table designs. I basically have several different types of work items that need to be tracked. Each work item has their own set of statuses, some of which may be the same as other work item's statuses, some of which will be unique for that work item.
Now, should I create one status table for each work item, or would it be better to use one status table, but add a column to track which work item the status belongs to?
I'm not sure which is a better design, or perhaps there is some alternate design that is better. Any help is appreciated.
Currently I need to design database table and got stuck.
I have a project and its sub projects. A parent project and its sub projects share the same requirement.The description of the requirement will be changed every day. For example Project A - Do Some thing 1 on Project A [09/28/2007] Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Every time the comments updated, both project A and project A-A1 should share the same update i.e select project A and its subproject will be displayed Project A Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] Project A-A1 Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29//2007]
When you tried to insert new sub project, the requirement will be shared with the parent project ie. Project A - Do Some thing 1 on Project A [09/28/2007] Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] Project A-A2 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] , Do Some thing 2 on Project A-A2 [09/30/2007]
assume Project A-A1 and Project A-A2 are sub project of project A
Could anyone show me how to design the database table for those mess?
I have a huge database and off lately, I have been experiencing some performance issues on some queries. Some of my tables now have over 10 million records. I noticed the performance issue was caused mainly from one table created as a work around. If I remove the column from the workaround table (but still leave the table in the query), I get the results in 1 second. However if I include a column from the workaround table, the same query takes 14 seconds (same output only an extra column) . I will appreciate any advise on a better design to get around my workaround table.
Below is my scenario
Table - Team ------------------- Column - TeamID (Primary Key) TeamName
Table - Matches --------------------------- Columns - MatchID (Primary Key) MatchName Team1ID (Foreign to "TeamID" in Table "Team") Team2ID (Foreign to "TeamID" in Table "Team")
Table - Players ---------------------- Columns - PlayerID (Primary) TeamID (Foreign to "TeamID" in Table "Team") PlayerName
The above tables work very well... The tables I listed below are my cause of issue... I need to write a query to list every match with the names of players who played in it comma delimited (one row per match).
I created a table
Table - MatchPlayer ----------------------------- Columns - MatchPlayerID (Primary) MatchID (Foreign to "MatchD" in Table "Match") PlayerID (Foreign to "PlayerD" in Table "Player")
But I couldnt write a query that would list me all the players comma delimited in one row per match..
This allows me to easily list the player names comma delimited per match but is a very bad design. Is it possible to list the same using the table Match Player.
need help here pertaining to the database table definitions
all was well when i started using it a month or 2 ago until now that when i tried to redesign my tables,it wouldnt prompt me to choose a primary key for my table
previously what happened was that when i saved the table without selecting an attribute as a primary key,it would prompt me to do so nu could choose to have the primary key auto-generated
but now it seems it stopped prompting me about it
whats wrong with it?anyone else faced this?
pardon my question,im a noob at this sql server thingie
