I am writing a database using SQL as the back end and Access as the
front end. My project is to create a rota for when people have to
work. The problem I have is setting up the tables so I can query them
correctly.
I currently have a staff table which holds the staff_id and staff_name
fields. I have a worktime table that has the staff_id, date, start
time, end time as fields. What I would like to do able to do is have a
query that returns the times a person is working in date order along
the row
i.e
I can't seem to work out a query that will do this easily. I can
think of ways of manipulating the data heavily but that defeats the
object. I wonder if my table structure is incorrect. Is it good
practice to have a table where each field is a date? Seeing this will
need to be referred to for year on year comparison it seems a bad way
of doing it. Basically is this the correct structure for a diary
application?
Any help from you great people out there would be superb! Thanks in
anticipation.
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
One company can have many site addresses; One company can have many trades And one trade can have many activities.
Ok seems very basic at this stage does'nt it, but if i give some data the complexity changes completly for example:
the trades are as follows: TRADE TABLE ID 1: DESC: Printing ID 2: DESC: Artwork ID 3: DESC: Photography ID 4: DESC: Reprographics.
now in this case only the printing trade has activities: TRADE ACTIVITIES TABLE ID 1: DESC: Flexo ID 2: DESC: Digital
So, in the above i am saying which ever company picks Printing as there trade, then they have the option of specifying which particular printing trade it is that they do.
Again simple enough by using one-to-many from the Trade Table to the Trade Activity table.
The problem is that not all trades have associated trade activities, for example the artwork trade activity is just artwork, but i need to be able to allow this to be so in the future.
When I try to create a view to select a particular company, with there associated trade and trade activity, I do not get any result at all because not all of the trade activities have any records assigned to them.
Please can anyone help.
I need to be able to pull back all companies with their associated trades and associated trade activities.
As my learning process continues, I have found that Database structure is probably the most important & most difficutl part of development. Like database normallization etc.
How can I master database structure, ? but I want learning other than any database software like Access or SQL Server, I m looking for a generallized learning.
Hello I am a final year student and for my final year project I have decided to try and create a price comparison website due to the fact that i feel it would be a good project to work on and develop my technical skills in C# and microsoft sql server 2005, since these are mainly the two programs I would say i have more experience with. Basically I want to create a website such as www.pricerunner.com. At this point in time i have started playing around with how i feel the database will look, in terms of tables and columns, below is what i have produced; Company Company_ID Company_name ADDRESS Website link contact Products Product_ID Product_name Product_Category Product_Price
I am now stuck as how i will link each company with a product bearing in mind a company will stock different categories of products, also do you think i need any more columns in any of the above tables, as well as am i missing out on anything, I am hoping someone with more knowlegde than myself can shed some knowledge on my problem, thank you.
Structure Number1 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Structure Number2 Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate Can anyone tell me that between above 2 structure what is better and why? I just add CategoryNativeId(P.K.) in Number 2 structure.
I'm sure there is a simple way to do this but I cannot think of it.I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.
Our company would like to start using SQL 7.0 Snapshot replication for some of our customers. My problem is that we often have to make changes to the actual structure of the DB. Will I need to disable replication each time we need to make a change to the db structure on the publisher and subscribers?? Thank you...
Hi All, I need to get some info on how I can get the script of the entire database stucture. I need to write a procedure to do this. I know in SQL Server I could get the script in a mouse click. But I need to write a procedure to do the task. Is there a package in SQL Server that I could use to accomplish this task? Any thoughts will be of help. Thanks in advance. sj
Could anybody tell me the detail structure of the .mdf file. Actully I want to read the mdf file with out using MS SQL 2000. I know few details like it has 96 bytes header and pages are divided in 8Kb etc. But I want to know the exact details, so that I can read the Table names and columns at least.
I am very new to SQL server, and am completely stumped here. I need to transfer the data from an old database to a new one (both SQL Server 6.5 DBs.) The only problem is that the database has been restructured, and I can't for the life of me figure out how to get the data imported. I am working on manually making all of the structure changes, but there must be a better way. Any ideas? Thank you SO much!
When you attach an MDF that was created using SQL 7.0 to SQL Server 2000, the MDF file structure gets updated. The resulting MDF can no longer be used by SQL 7.0.
My Question: does anyone know how to read the file structure version of a detached MDF file?
If the file structure is 7.0, I would like to warn the user and allow her to cancel the attach + conversion to SQL 2000.
I a DB deployed in the field at numerous customer sites. I have made changes to the DB in my dev environment and now I need a way to update the DBs out in the field to the new DB structure. Examples of updates are adding a new column to a table, or adding some more code to a stored proc.
So what do I do ? I need to be able to email comething to the customer that they can run that will make my recent updates to their DB structure. It can't screw up their existing data either.
So what's the solution ? A big SQL script wil ALTER TABLE commands ? How do I updated stored procs through a script ? I need help seriously. What is the accepted industry standard method of updating deployed DBs when a new version is released ? Please Please help.
I have 2 databases with corresponding history databases. As the system is under development, the 2 databases are changing in structure i.e the table structure(new tables as well as changes to existing ones) and indexes will change every week. What is the best solution to replicate the changes on the history database.
This is a sort of database structure question that could I guess apply to any type of database...
Say I have 4 production lines and I want to log serial numbers on the start of every line and off the end of every line.
Is there any advantage in having a separate table for each Production Line and serial number logging point? (i.e. t_Line1Start, t_Line1End, t_Line2Start etc) or is it just the same as having one big table and having a LineNumber field and a StartEnd field?
Is SQL Server just as happy dealing with fewer transactions to several tables as it is to many transactions to one table?
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials. 2) I need to create hardware using labour and materials. 3) I need to be able to include some hardware in some products. 4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts ( ProductID int NOT NULL, Product varchar(50) )
go
ALTER TABLE tbProducts ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID) GO
CREATE Table tbMaterials ( MaterialID int NOT NULL, Material varchar (50) )
GO
ALTER TABLE tbMaterials ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID) GO
CREATE Table tbLabour ( LabourCode char (2) NOT NULL, Labour varchar(50) )
GO ALTER TABLE tbLabour ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode) GO
CREATE Table tbProductMaterials ( fkProductID int NOT NULL, fkMaterialID int NOT NULL, Quantity Float NOT NULL )
GO ALTER TABLE tbProductMaterials ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID) GO
I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data. Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!
I will be really thankfull if somebody can help me on this issue.
hi all.. if im about to start a new database.. how do i start? please advise on how do i start sketching a new db structure... how do u guys set up ur db table struc.. any software to do this designing? any links or tutorial will be appriciated..
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s) An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users) userId | timestap | message 1 12/12/2007 10:00 some message 1 12/12/2007 11:00 some message 1 12/12/2007 12:00 some message 1 12/12/2007 13:00 some message 1 12/12/2007 14:00 some message 2 12/12/2007 10:00 some message 2 12/12/2007 11:00 some message 3 12/12/2007 12:00 some message 3 12/12/2007 13:00 some message 3 12/12/2007 14:00 some message ...etc..
I need to create a table that supports a potentially infinite amount of subcategories. An example would be: Food Pastries(Parent=Food) Cupcakes(Parent=Pastries) Chocolate(Parent=Cupcakes) Vanilla(Parent=Cupcakes) Danish(Parent=Pastries) Meat(Parent=Food) Beef(Parent=Meat) Chicken(Parent=Meat) Cajun(Parent=Chicken) Fried(Parent=Chicken)
The subcategories will vary in depth.
Can someone demonstrate a table create statement and then the proper select to pull this type of data?
Is there a way to back up the structure of the database only? i.e.backup without the data.I sometimes need to backup a database in one location and compare thestructure in another, can be a pain if there's 600MB of data that goeswith it.
