User Configurable Table Designs
Jun 12, 2007
To the pros:,
I have one question about designing tables and interface (web/windows
applications). The requirement is like this.
I want to design an application to manage upload / review / manage
files which has some meta data associated with them as well. The files
can be any documents. This application can be used in different
companies same way, but each company wants to use different field
names.
So, I want to design tables generic enough to allow custom labelling
fields. So if I have a table
T1 with ID, FileName, FileSize, FileAuthor.... some company may want
to use different field names. What's the best way to design tables
like this and how to maintain relationships, stored procs, triggers
etc linked to these tables.
Many thanks in advance,
DS
View 1 Replies
ADVERTISEMENT
May 14, 2008
I'm currently developing an ASP.NET website which is using SQL Server 2005 and I couldn't decide between two table designs and I hope you can give me your opinions
The website is for a school and it'll be used to create tests from questions. The teacher will:
1. Select grade (could be multiple selection)
2. Select class
3. Select subject
The thing is that same question could be used for multiple grades.
Example query: "Get me questions of trigonometry of math from grades 7,8,9"
(Names used instead of ID's to make it more clear)
The first design:
[BigRelationsTable]
ID - QuestionID - GradeID - ClassID - SubjectID
1 - Question123 - Grade7 - Math - Trig
2 - Question123 - Grade8 - Math - Trig
3 - Question123 - Grade9 - Math - Trig
This is a simple design but all of the columns will need indexes because all of them will be used for searching and that makes me think about table performance.
Second design:
[GradeClassRelations]
ID - Grade - Class
1 - 7 - Math
2 - 8 - Math
3 - 9 - Math
[ClassSubjectRelations]
ID - GradeClassRelationsID - SubjectID
1 - GradeClassRelations1 - Trig
2 - GradeClassRelations2 - Trig
3 - GradeClassRelations3 - Trig
[SubjectQuestionRelations]
ID - ClassSubjectRelationsID - QuestionID
1 - ClassSubjectRelations1 - 1
2 - ClassSubjectRelations2 - 1
3 - ClassSubjectRelations3 - 1
This one is more normalised but this time the need of doing multiple joins makes me wonder.
What do you think? Which one should I use? Or if you have any other suggestions I'm all ears
View 2 Replies
View Related
Nov 19, 2007
Is it possible to configure the Sql Server log provider so the log server can be changed via a configuration?
I want to do this without redeploying packages and without errors.
I have a problem when I try this: A package was developed using Sql Server log provider on ServerA. The log provider connection was overridden by a Sql Sever configuration. The package was deployed into an environment where ServerA is not available, and the log connection overriden with ServerB (via a Sql Server Configuration). This results in a connection error saying SSIS failed to connect to ServerA. If errors are ignored, then the package continues and produces logs on ServerB as required. We are running SP2.
-------------------------------------------
I ran more tests from the command line and found the problem only exists within Visual Studio. When run from the command line the package configurations kick in before logging starts. So that will work fine in production where we will be running from the command line. I will leave the post here as I have needed to spend some time on this issue and I am sure others have too.
It would be good if someone could provide full documentation about the order of events as a package starts - both within Visual Studio and from the command line.
View 7 Replies
View Related
Jan 17, 2008
Hi,
is there any website or book that provide excellent database design examples to learn?
Appreciate for any help
Ricky.
View 1 Replies
View Related
Dec 18, 2007
Hi All,
I am creating a script to open an Excel file and copy the data into a couple of SQL Server2000 tables.
I am using OPENROWSET to open the file but I need to make it configurable so that I can change the name of the Excel file and name of the sheet.
Below is my code that works fine:
I would like to make the file name: CORAL 49pt.xls configurable as well as the path to the file.
In addition, I would like to make the sheet name configurable: 100nm$
I tried using QUOTENAME but it gives me an error.
Thanks!
Gloria
DECLARE SiteThickness_Cursor CURSOR FOR
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:Program FilesICE300 V2.2.2ThicknessFilesCORAL 49pt.xls',
'Select Site, [X Pos], [Y Pos], Thickness from [100nm$] Order By Site ASC')
OPEN SiteThickness_Cursor
FETCH NEXT FROM SiteThickness_Cursor
INTO @SiteNumber, @XCoordinate, @YCoordinate, @Thickness
WHILE @@FETCH_STATUS = 0
BEGIN
--Round the X and Y coordinates to 4 decimal places
--
--Add values into the tblSiteThickness table
INSERT INTO tblSiteThickness (idWafer, iSiteNumber, dXCoord, dYCoord, dThickness)
VALUES (@WaferID, @SiteNumber, ROUND(@XCoordinate, @DECIMAL_PLACES), ROUND(@YCoordinate, @DECIMAL_PLACES), @Thickness/@ANG_TO_MICRONS)
FETCH NEXT FROM SiteThickness_Cursor
INTO @SiteNumber, @XCoordinate, @YCoordinate, @Thickness
END
CLOSE SiteThickness_Cursor
DEALLOCATE SiteThickness_Cursor
View 1 Replies
View Related
May 3, 2007
Hello
guys and girls, I am using XML for the first time to send the information read in my tables. I want the XML to take the information and send it in an email. With that I want it to be configurable so if I want to change that email address I can.
Anybody help pls
View 1 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
Jul 20, 2005
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies
View Related
Jan 17, 2006
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
View 5 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
May 23, 2007
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
View 9 Replies
View Related
Jun 23, 2006
We are in the early stages of developing a small project and I have a question I would like an opinion on.
When deciding how to design a table to store information on users who will be different types.
The project involves setting up webpage that allow " appointment setters" to book demos for our Sales reps. We want to keep track of how many each appointment setter books.
I have a user table with user_id, fname,lname, type. Where type represents the type of users they will be either "appointment setter" or "Sales Rep".
In the Appointment table I would like to store the Appoinment setter ID, sales rep Id, date/time appointment was booked and some other data on the appointment.
My question is how do I set up the relationship from the appointment table back to the user table.
Since each record in the appointment table will have a App Setter ID and a Sales Rep ID.
Should I just pick either the app setter id or the sales rep id as the foreign key back to the userID in the user table. Or is there another method how to handle this.
Any thoughts or articles would be greatly appreciated.
thanks,
View 1 Replies
View Related
Jan 19, 2008
ok suppose i want to create a table with a user specified name...... how should i replace the name with the variable name="table1" Dim condb string ....... Dim cmdDB2 As SqlCommand Dim name as string=("table1") Dim selkta As String = "CREATE TABLE name(no int,quest text,op1 text)" cmdDB2 = New SqlClient.SqlCommand(selkta, conDB) conDB.Open() cmdDB2.ExecuteNonQuery() conDB.Close()thank u
View 4 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Nov 29, 2007
I have an application (vb 2005) that reads 8 different tables in 8 different data base and builds a table for the selected records (using sp's). This table is used as basis for building a data grid and cosequently export to crystal report.
My question: How can I create a table for each user, so multiple users can run the same program at the same time. I tried using #temptable but that did not work. Can I in a sp create a table with a name that contains the user id passed to the sp as a parm?
View 1 Replies
View Related
Jul 20, 2005
I am new to the database. I have a user table.Currently, when I open this table thru Enterprise, I can see allpasswords and usernames.Is there anyway I can make this table more secure?Any tips for handling user table will help.
View 1 Replies
View Related
Nov 7, 2006
Hi, i want to make a poll, i mean a user logs in and vote, the vote i want it to be an insert onto a table containing only the answer so when all users have voted i'll count the results. My problem is, imagine 2 people submiting at same time, will they be able to insert on the table sucessfuly? Do Sql Server allow shuch an operation? Please tell me if i'll have problems and what have to do, also the best method to do a poll. Thanks in advance.
View 1 Replies
View Related
Feb 13, 2008
Hey,I have a fairly large table for my keeping my information about users. My question is, would it be better to separate it, for example, create another table that has all the personal information (like city, street, etc)and have it related to my other table where more of the data-ish information is kept? Thanks,Sixten
View 3 Replies
View Related
Jun 14, 2004
I'm new to SQL Server.
I've an online Database on a server. I've my own user name like "AKR". I want to create every table under that user. But what happens is whenever I create a table online it goes under "DBO" user name. How to avoid that?
Thanks
View 1 Replies
View Related
Jun 5, 2001
My problem is that I can't query a couple of tables. I created them using DTS myself, in a db called contracts, and called one of them 010425. I've logged in as SA, and making sure the correct DB is shown in Query Analyser.
Here's the query...and very simple it is;
SELECT TOP 10 * FROM [dbo].[010425]
I just keep getting the following error message;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.010425'.
But it shows up in Sysobjects and EM!
I can query the system tables, using EXACTLY the same syntax...no problem there.
I've got SQL Server 7 running on W98. Everything is happening locally.
Any ideas what I'm doing wrong, people?
Thanks in advance for your help,
Jaishel
View 1 Replies
View Related
Aug 21, 2001
I want to include the name of the user whom so ever has changed the record.
I want to insert the name of the user in the column.
How to grab the name of the user from the action...?
Thanks,
Harish
View 1 Replies
View Related
May 8, 2008
I have set up a couple of views for a user but they want to be able to see the database table columns by doing right click on table and getting the columsn.
I tried to give the permissions to database by right clicking on database and doing the user and giving view defintion.
-- [TABLEA] contains no columns that can be inserted or the current user does not have permissions on that object.
User not allowed to select the data .
Thanks
View 2 Replies
View Related
Feb 14, 2008
Hi i want to create a page, which a user enters their email address, and ticks a few tick boxes against items they would like to be notified of. The user doesnt have to be a member of the site, how should i structure my table in the database, thank you.
View 5 Replies
View Related
Jun 12, 2005
Hi,I using vb.net to do this. I need to retrieve data from a table name "User". The table is in a sql server. I having trouble retrieve data from it. This is my code
Dim strsql As String = "SELECT DISTINCT NameID FROM User"Dim cmd As New sqlCommand(strsql, cn)Dim das As New sqlDataAdapter(cmd)Dim da As DataSet = New DataSetdas.Fill(da)This is the error message.
Syntax error in FROM clause. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Source Error:
Line 31: Dim das As New sqlDataAdapter(cmd)
Line 32: Dim da As DataSet = New DataSet
Line 33: das.Fill(da)
Line 34:
Line 35: DropDownList1.DataTextField = "NameID"I have tried other table, no problem, data is retrieved. Seem like "User" is restricted word... Worse is i cannot change the table name, only thing i can do is get data from it. So hope someone can help me on this. thanks in advance!!
View 2 Replies
View Related
Dec 2, 2005
Hi all,
I want to have a query that will return me a single row for each user
from a table where the table has many rows for each user. The single
returned row for each user must be the most recently dated entry
([I7-Change-Date]) for that user.
An example of the code I have so far is as follows, but it obviously doesn;t work.select DISTINCT([I1-Customer-Ref]) AS Cust,([i7-w-fixed-amnt]) as WaterFixedAmt, ([i7-w-rv-amnt]) as WaterRVAmt, ([i7-s-fixed-amnt]) as SewerageFixedAmt, ([I7-Change-Date]) AS [Date]from r07UnMeasuredBills ORDER BY Cust, [I7-Change-Date] DESC
I am using MS SQL Server 7 for this.
Thanks
Tryst
View 6 Replies
View Related
Nov 25, 1998
Can I declare variable and assign a user defined table name in Sql server 6.5. if so how can I do that ,
Thanks
Ali
View 1 Replies
View Related
Apr 13, 2006
Hi!
I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.
CREATE TYPE employee_t AS OBJECT (
name VARCHAR(10)
jobDesc VARCHAR(15)
...
)
CREATE TABLE Employee of employee_t
Regards,
Timo
View 3 Replies
View Related
Nov 20, 2014
I dont know how to get the newest input for each user from one single table.
Should be a very simple task but i cant work it out.
The table looks like this:
ID (A_I), userID, ip, date(timestamp)
Here is a SQL Fiddle Link with some data also: [URL] ....
I have tried a lot querys like this one:
SELECT userID, ip FROM userips GROUP BY userID ORDER BY ID DESC
But this one does not give me the latest ip which was entered by a user.
View 2 Replies
View Related
Jul 20, 2005
Hello,I have an application that will be logging to a SQL Server 2000database user user activity from several Windows 2003 terminalservers. This information will be retrieved by monitoring theSecurity logs of these servers (this part I know how to accomplishalready).A table in the database, tblLogEntries, will contain the followingfields:- ID = autoincrementing int- LogTime = Date/Time the user activity was recorded in the securitylog- Username = User's login ID that the activity was recorded with- Type = int, referencing a lookup table with the values of Logon,Logoff, and possible other future items- Server = The name of the server the activity was recorded on.The only question I have is, can you offer a way to process the totaluser login time during a given range using T-SQL.For Example...Given the table data:ID LogTime Username Type Server1 10-10-2003 8:30:00 Tom Logon SERVER-A2 10-10-2003 8:45:00 Sarah Logon SERVER-A3 10-10-2003 16:45:00 Tom Logoff SERVER-A4 10-10-2003 17:00:00 Sarah Logoff SERVER-A5 10-11-2003 8:30:00 Tom Logon SERVER-A6 10-11-2003 8:45:00 Sarah Logon SERVER-A7 10-11-2003 16:30:00 Sarah Logoff SERVER-A8 10-11-2003 17:15:00 Tom Logoff SERVER-AHow would you receive the output:User Logon Total Time for SERVER-ATom 17.0 hrsSarah 16.0 hrsI know I can handle this type of processing on my ASP.NET front-end,but I'm curious as to how easily it can be done by the database,itself.Thanks in advance for your assistance.
View 4 Replies
View Related
Mar 12, 2008
I'm using an Execute SQL Task Editor to try and load the last load date into a user var. I don't know what I'm doing wrong.
I have result set to Single Row and a simple query:
SELECT LastLoad
FROM HeidtmanDW.dbo.CubeLoadStats
WHERE (SourceTable = 'Sales')
Where do I set the user var User:LastSalesLoadDate (which is scoped to the entire packge)?
Thanks.
View 6 Replies
View Related
Sep 25, 2007
Just out of curiosity, could someone point me towards a listing of the user permissions for the MSDB table? I have looked through BOL and on the internet and cannot find a good listing. An example would be something like...
dts_admin: <dts_admin description>
Thanks in advance.
-Kyle
View 1 Replies
View Related
Nov 9, 2006
Hi all...
Iam a newbie and i have one question want to ask experts ... .
I am current working on one App which have some user connect to database at the same time.
And i want to know: how many user connect or use (read , update) to one row in a table?
Could it be possible to know that??? If you have one solution to solve this problem , please let me know :) ...
Could it be done by Software , T-SQL or anything ...., iam happy to know.
Thanks all.
View 8 Replies
View Related
Jun 22, 2007
Hi,
Question:
I just moved a database from one server to the other and now I'm getting an error when I try to modify a field in my table from management studio:
- sql2005 the user does not have permission to perform this action.
I made sure to remove all users from that database once i got it up and running on my new server and made sure I didn't have orphaned users.
I created a new userid and gave it full dbo rights. I planned on restricting permissions later.
Is there something new to check in 2005 that would prevent a user with full dbo to modify data in a table?
The table has no constraints or dependencies, it's basically a lookup table so very simple stuff.
Thanks, Seb.
View 7 Replies
View Related
