Database Structure And Presentation
May 14, 2008
I'm trying to design a site for parent conference scheduling.
Usually we have a couple of days appointment time, for example 5/8/2008, and 5/9/2008. and the time usally start at 7:00 and ends at 9:00. one appointment time lasts about 20 minutes.
I would put a page something like this, after parent or student login, 1. they are asked to choose a date, 2.after the date is chosen, show available teacher names, 3, after the teachers are selected, for each teacher, it will show a list of radio buttons for parents to select the time period open, For example:
teacher: John smith.
time: 7:00; radio button
7:20 radio button
7:40 Booked
...............
8:40 radio button
4, the student choose the available time, and then enter student name, then submit.
I would like to know how to setup the database table and present to the users. Currently for this scheduling part I have tables like teachertable, scheduling table, Appointment table.
Teacher table: teacherID, name, email, course,
Scheduling table: appointD, TeacherID, room, appt date
I think I will prepopulate the two tabels before conference time.
and after user submit the selection, insert into Appointment table the other fields like scheduling ID, appoint time, and studentname.
The things confuse me, is how can I deal with the appointment time fields, shall I add another table for appt time? should this field be prepopulatd to the table and pull from the table for student to select or something else?
HOw can I do with this: if a teacher not available after 8, how can I excluded the time period for the teacher?
Thanks
View 1 Replies
ADVERTISEMENT
Jun 2, 2006
I was demonstrating some of SSIS's capabilities to our Software Development Manager and VP of IS. (We are deciding which ETL tool to use for our DW project. So goes this project goes the house).They liked what they saw and were crazy over Fuzzy lookup but commented that SSIS seems very much oriented toward the programmer side of the house. It was, maybe, not the best tool for the Business analysts who are working closely with us on this project.
Any comments on their comment?
I wonder if SSIS "data source views" (which I haven't looked at at all) address their concerns?
Barkingdog
View 5 Replies
View Related
Mar 16, 2004
I need to report on what users have what permissions. This is the way we have it stored currently. There must be a better way to store or retrieve this info. in a warehouse. Ideas?
Thanks,
-Kevin
USER readwriteexecutechange UIcreate proc.
joexxx
billxx
bobxxx
View 6 Replies
View Related
Aug 8, 2007
I have a table with Columns as metric id, product name, product id, sales, amount.
The values of the column metric id will be 101, 102, 103 etc..
I need to create a report, in which I need to show up data as below:
1 series -------
metric 101:
product name, product id, sales, amount
metric 102:
product name, product id, sales, amount
metric 103:
product name, product id, sales, amount
2 series--------
metric 201:
product name, product id, sales, amount
metric 202:
product name, product id, sales, amount
metric 303:
product name, product id, sales, amount
Please let me know, how I can show the rows of that table grouped for each metric id.
View 1 Replies
View Related
Feb 24, 2006
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 ?!
View 3 Replies
View Related
Jan 6, 2008
Hi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
View 3 Replies
View Related
Jun 27, 2006
Is there a way to retrieve a returned value from a stored procedure w/out binding do a data presentationi control? I have the following sqldataprovider...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="f_GetUser"
ConnectionString="<%$ ConnectionStrings:MyServer %>"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:formparameter name="username" formfield="txtusername" />
<asp:formparameter name="password" formfield="txtpassword" />
</SelectParameters>
</asp:SqlDataSource>
Do I have to make a gridview to get the value that is returned from this stored procedure? The SP only returns one value (a count of records) and I want to set it to a variable. Is there a way, similar to a recordset in ASP, that I can access that one value?
here is my stored procedure...
CREATE PROCEDURE f_GetUser
@username varchar(50),
@password varchar(50)
AS
SELECT COUNT(*) AS numusers
FROM f_Users
WHERE username=@username AND password=@password
I just want to retrieve "numusers" and use it in a conditional....
tia for your help
View 3 Replies
View Related
Mar 14, 2006
Values of two columns in two different tables--presentation usingselectHi Everyone,i have two tables in the database . One is called address tableand one is adressPhone Table. Below is the sample of those two tablesAddresscol1 col2 col3X 12 13y 15 19z 18 10create table address(col1 varchar(20),col2 int, col3 int)insert into address values ('x',12,13)insert into address values ('y',15,19)insert into address values ('z',18,10)AddressPhoneCol4 Col5 Col613 213-455-9876 113 415-564-6546 213 543-987-5677 319 678-555-2222 1create table addressphone(col4 int, col5 varchar(50),col6 int)insert into addressphone values(13,'213-455-9876',1)insert into addressphone values(13,'415-564-6546',2)insert into addressphone values(13,'543-987-5677',3)insert into addressphone values(19,'678-555-2222',1)I have to display something like thisx 12 13 213-455-9876 415-564-6546 543-987-5677y 15 19 678-555-2222 NULL NULLSo there is one to many relationship between address andaddressPhone table where address.col3 = addressphone.col4I don't know how to write the query to get the phone numbers thathas he same id in the same row like I displyed above.I did something like this, but this is not workingselect col1,col2,col3, (select col5 from addressPhone where col6=1),(select col5 from addressPhone where col6=2), (select col5 fromaddressPhone where col6=3),from address table inner join addressPhoneon address.col3=addressphone.col4above is not working because it is complaining that a subquerycannot return multiple results. Col6 in the addressphone table isthe phone type ike business phone,mobile phone or home phone. It is possible that there are two phonenumbers for business phone.Please let me know how can I write this query.Any help will be greatly appreciated.Thanks
View 4 Replies
View Related
Feb 13, 2007
Hi, I'm looking for SQL Server 2005 Reporting Services presentation.
Anybody help, please.
Thank you.
View 1 Replies
View Related
Sep 25, 2007
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.
View 5 Replies
View Related
Aug 10, 2005
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.
View 5 Replies
View Related
Oct 9, 2001
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
View 1 Replies
View Related
Sep 2, 1999
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!
Jeff
View 1 Replies
View Related
Nov 1, 2004
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?
View 1 Replies
View Related
Apr 9, 2008
Hi,
I have a question. I want to build a db for the following entities:
drivers
cars
companies
each driver nay has a car (NULL or carId)
each driver belongs to company
each car belongs to company
The db structure I built is:
car(carId, companyId)
driver(driverId, companyId, carId)
company(companyId)
The problem is that in this structure driver from company a can have a car from company b.
but this situation is not possible.. How can I prevent such situation??
tnx
View 14 Replies
View Related
Jul 8, 2006
Is there a way I can copy my SQL 2000 database including procedures, views tec. the whole works but not the data in any table.
I want to share with a relative a copy of my application (web based) he has his own SQL 2000 server but I do not want to give my data just the database so the application will run and he can start inputting his own data.
How can I do this like a backup which has everything but with no data.
I tried scripting it using the EM but it did not give me everything just the system db's
Thanks
Xavier
View 3 Replies
View Related
Jan 11, 2008
I'm designing a web application that acts as business management software for many small autobody shops. This method was chosen because it creates a central point of administration for the software and I believe that a properly configured cluster of services will meet performance requirements. Using SQL Server 2k5 as my backend, I need to structure my DB(s) so that performance and size are kept to a minimum.
That being said, the first problem I encounter is in creating a structure that supports multiple businesses, each with their own unique dataset.
For example, each business will have work orders specific to their business. This necessitates a uniqueness constrained by businessID (hereafter titled "BizID") and WorkOrderID (WoID). The first solution I happened on was the simplest: A composite primary key composed of BizID (int) and WoID (int). The unfortunate consequence of this, as I soon realized, was that *every* table containing multiple business' data would need this composite key structure. The second problem with this method is that there is no such thing (to my knowledge) as a composite identity. I could identify WoID as the identity column, but it would not iterate WoID for BizID x properly if any other Biz added a work order to the table. (i.e. biz 1 adds three work orders (identitied 1, 2, 3), biz 2 adds one work order (would identity as 4) and Biz 1 adds another (would identity as 5), leaving us with 1, 2, 3, 4, 5 across two businesses, rather than 1, 2, 3, 4 for biz 1 and 1 for Biz 2)
The second idea I considered was one DB per business. I didn't consider that for very long, since I assumed size considerations would be blown out of the water if I had 50 business and 50 DBs. I could be wrong, as I am decidedly a novice at this. It does benefit from not putting all my eggs in one basket though and makes backup/recovery/troubleshooting much simpler.
The third idea I considered was one table per business. This suffers from the same issues as #2 above with not as many of the benefits.
Both ideas 2 and 3 would need a master table relating logins to the DBs they belong to, which would not be hard to implement at all, but the implications of using this method are unknown to me.
So quite obviously, I'm a novice at this. Perhaps I took a project on that was too big for my britches, but just getting out of college puts me in a good place to take a risk and I will learn a great deal from putting a complex (is it so?) system like this into action.
I appreciate any and all responses, thanks in advance for your replies!
-Devin
View 5 Replies
View Related
Sep 1, 2006
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
View 3 Replies
View Related
Oct 3, 2006
I have one databaseand I create new database by copy everything from first db I then alter some column on new database and now I would like to compare two databaseHow can I do it ?
View 4 Replies
View Related
Jun 4, 2007
I am thinking of doing a fake PC company site for my ASP project. so
what they will have is a chat, products with reviews, and users can
have "Buddies".So my DB so far (Tables):ProductPC - ID, Processor, RAM, HDD, Graphics, LCD ...ProductHardware
- ID, Title (Like "Intel C2D E6600"), Description, Price, Rating (0-5
stars, so integer), Category (CPU, HDD, Graphics)ProductSoftware - ID, Title (eg. "Adobe CS3"), Description, Price, Rating, Category (eg. Design, Programming)Reviews - ID, ProductID, Title, Content, DateChat - ID, TopicID, Title, Content, DateUsers - ID, Username, Password,The problem, how do i connect Reviews to the products since they are from diff tables.How do i get the "buddy" system workingChat i think its not as simple as thatBut i just need a simple ASP project, so no need to get too complex, but i still hope to learn as much.
View 12 Replies
View Related
Nov 26, 2007
Hey,
I havent been working with databases for quite a while but now im in charge of creating a rather powerful website with webzine (online magazine) among other things. Within this magazine there are several different categories of articles such as concerts, album reviews, interviews, product reviews etc etc which obviously recquire rather differently structured tables. Multiple images are going to be uploaded to all articles and users are going to be able to comment on all the articles. Since the comments and image tables dont differ between the articles i would much rather store all comments for example in the same table, but wont i get relationship problems? Perhaps I could have a column referring to a certain kind of article, and then a second column with the id of that specific article? Or is the best way simply to have a seperate image and comment table for each set of articles?
View 4 Replies
View Related
Jan 29, 2008
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
View 7 Replies
View Related
Mar 31, 2008
Hello all!
I'm working on an application that requires a bit of tricky database development, and I was hoping to get some advice from you.
The best way to describe the application, is to use an example, and the best I can think of is Facebooks news feed, where you can see if any of your friends have become friends with other members, or joined any groups, if your friends have completed quizs, attended any meetups, got arrested at the weekend etc etc..
My application is similar, but requires a lot less features. It will only tell show you if any of your friends have become friends with other members, or joined any groups (example: your friend Dave is friends with Jane).
Currently in my database, I have... users table (that holds a list of registered users) friends table (that links users who have decided to become friends with other users)groups table (that holds different groups)user_joined_groups table (that links users to groups they have decided to join).
Its simple and it works fine.
So to get around the newsfeed problem, I thought of doing this, creating 2 new tables. The first table will hold the news type and give it an ID...
NewsTypeTable============NewsTypeID NewsType1 NewFriend2 JoinedGroup
The next table will be FriendsNewsTable, and everytime a user joins a group, or makes a friend with another user, details are added in here...
FriendsNewsTable=============NewsID NewsTypeID FriendID newFriendID newGroupID datecreated1 1 225 156 2 2 95 52
Ok, so above in the FriendsNewsTable, the first record shows that the user has made friends with someone, and the second record shows that a user has joined a group. I though that this approach might work. but then I though... If I have 20 friends, that means I am going to have to search with each one through FriendsNewsTable, which could contain 30,000 records (wishful thinking!) So this is bound to make the page crawl to say the least.
So does anyone have any advice how I should go about this?
Thanks!
Matty
View 4 Replies
View Related
Apr 26, 2008
What method would you suggest is the best way to deal with a website?
Say for example, writing SQL. Am I better using Stored Procedures and adding methods for accessing there or just writing the SQL inline. What do you think about this? Is it totally overkill to use stored procedures in a small website?
View 6 Replies
View Related
Sep 30, 2005
Hello,I am upgrading my (simple) cms from one menu layer to two layers. So i have two tables in de my DB- table_mainmenu- table_submenuThe submenu records are linked to the mainmenu records on the mainmenu id. So far so good. Heres my problem, i cant figure out how to change the order of the pages...!? What is the theory behind recording the stacking order of the menu items? How can i record this information in the DB?I just need the theory, i can figure out the details for myself. Thanks in advance!Corstiaanp.s. is the the right forum to post this?
View 4 Replies
View Related
Dec 27, 2000
Hi all!
I have a friend who is a contract developer working remotely for a company on a SQL Server + VB project. He is having difficulty because someone at the company is making changes to the database tables without informing him, and of course then there are numerous application errors. This has happened far too many times for him to dismiss it. Examples of these changes are changing field names, deleting fields, changing datatypes, even changing the server name. My question is does SQL Server automatically log changes to the database, and if so, how can this log be read?
Thanks!
Lia
View 3 Replies
View Related
Apr 13, 2000
Can anyone tell me;
Is there anyway to copy an entire database structure (layout of column definitions and character formatting)?
Or do I have to delete the relationship, then the child first?
For testing purposes, if I screw one up, I would like to be able to start with another copy.
Thanks
View 2 Replies
View Related
Nov 1, 1999
Hi all,
I want to copy a production database schema to an archiving database, only
the structure : tables, views, defaults, rules,UDTs. For achieving that
I am trying to use SQL/DMO functions, "CopyAllTables" for example. My script works with no error but unfortunately the destination database
contains no objects. Why, have I missed something ?
Does anyone know how to do that?
Many thanks for your help
Regards,
Herve
hmeftah@aol.com
View 1 Replies
View Related
Jul 23, 2005
Hi.We have a .NET programmer using our SQL server. The database is roughly8GB at this point, full of all of our inventory, payroll data, etc. forrunning reports.This is on a dual-xeon 3.2Ghz, 4GB DDR, 3x15k RPM SCSI RAID-5, gigabitethernet, etc server.Yet, every once in a while it slows to an absolute crawl. IIS6 is thefront-end, and its limited to a web garden with 4 threads. However,sometimes only 1 processor does any real work, even when multiplepeople are running reports.Blocking is NOT a problem, however I've seen 300,000+ locks/secrequested occasionally (not waiting or anything like that, justopened).Cache hit ratios are roughly 99%.One issue that I suspect is that the programmer is using varchar(50)for EVERY column in EVERY table (except unique keys).My question: In this configuration, would varchar(50)s be the cause ofthe awful slowdowns?I know its all cached in memory, but on these older xeons (533fsb,DDR266) the memory subsystem could be better. If we are pulling THATmuch excessive data on an already congested bus (since its dualintels), would it cause a dramatic slowdown?The reports we run are highly intensive, but usually do not take afraction of the time that they do when the server exhibits thesebehaviors.I really could use anyone's input on the matter. I've read onlineguides, 2 performance optimization books, and seemingly created optimalindexes, but none of that has led me to a real solution.ThanksMortrek
View 6 Replies
View Related
Feb 15, 2007
Hi,is there a way of exporting database structure not the data in it?Thanks.
View 1 Replies
View Related
Jan 25, 2008
Hi all,
Is there a way to copy a structure of one database (tables and fields) to another database WITHOUT copying the data??
Someway to do that with replication??
any help is very appreciated!
Thank you,
View 4 Replies
View Related
May 21, 2015
Inside of stored procedure you have the code
create table #datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into #datatable
exec ('select *
from datatable
where id = 1')
select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after
insert into #datatable
exec ('select *
from datatable
where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
[URL]
View 5 Replies
View Related
Dec 29, 2007
Hi i am creating a web page which will return the results of products, the store name and the distance of the store from the postcode which the user has put into a text box. My first question is how would my database structure look, below is what it looks like at the moment;
Company; companyID, companyName, companyAddressLine1, companyAddressLine2, companyPostcode.
and
Products; productID, productName, companyID, price.
In order to achieve the proximity search how would i alter the "Company" table, as in what columns will need to be added or removed, thank you.
View 3 Replies
View Related