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
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 RelatedInside 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]
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
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
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.
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
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
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 RelatedHi, I'm looking for SQL Server 2005 Reporting Services presentation.
Anybody help, please.
Thank you.
Based on a table like below I have created a report so that I can compare number of items in the main warehouse (LOCATION1) and the outlets (LOCATION2 and LOCATION3).
___________________________________
| ID | PRODUCT_INDEX | LOCATION | VALUE |
___________________________________
| 1 | INDEX1 | LOCATION1 | 1 |
___________________________________
| 2 | INDEX1 | LOCATION2 | 1 |
___________________________________
| 3 | INDEX1 | LOCATION3 | 0 |
___________________________________
| 4 | INDEX2 | LOCATION1 | 0 |
___________________________________
| 5 | INDEX2 | LOCATION2 | 0 |
___________________________________
| 6 | INDEX2 | LOCATION3 | 1 |
___________________________________
| 7 | INDEX3 | LOCATION1 | 1 |
___________________________________
| 8 | INDEX3 | LOCATION2 | 0 |
___________________________________
| 9 | INDEX3 | LOCATION3 | 1 |
___________________________________
The way I present data in my Report is as such. I want to show items that are available in the warehouse that should be moved to the outlets.
select
a.PRODUCT_INDEX
, a.LOCATION1(VALUE)
, b.LOCATION2(VALUE)
, c.LOCATION3(VALUE)
from
[Code] .....
__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1 | 0 | 1 | 0 |
__________________________________________________________________
| INDEX2 | 1 | 0 | 1 |
__________________________________________________________________
| INDEX3 | 1 | 0 | 1 |
__________________________________________________________________
I have added some parameters in my report to filter out products that are not available in warehouse (LOCATION1) and this works great.
select * from VIEW where 'LOCATION1(VALUE)' > 0 and ('LOCATION2(VALUE)' = 0 or 'LOCATION3(VALUE)' = 0)
__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1 | 1 | 1 | 0 |
__________________________________________________________________
| INDEX3 | 1 | 0 | 1 |
__________________________________________________________________
Now the issue starts when I add a parameter to my report for user to choose which outlets (LOCATIONs) he wants in the equation. I know how to make a column disappear based on parameter value but how to take it out of equation? At the moment when user selects only LOCATION2 and not LOCATION3 then data is not filtered correctly:
__________________________________________________
| JOIN_ON_VALUES | LOCATION1 (VALUE) | LOCATION2 (VALUE) |
__________________________________________________
| INDEX1 | 1 | 1 |
__________________________________________________
| INDEX3 | 1 | 0 |
__________________________________________________
Ideally I would like a user to select random outlets (warehouse would be static on the report) and compare one or multiple and only show records that are 0 in the outlets.
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.
View 7 Replies View RelatedI 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?
View 6 Replies View RelatedHi, 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.
Thanks for any help
CREATE TABLE [dbo].[table1] (
[aaa] [bigint] IDENTITY (10000, 1) NOT NULL ,
[bbb] [int] NOT NULL ,
[ccc] [int] NOT NULL ,
[ddd] [bigint] NOT NULL ,
[eee] [int] NOT NULL ,
[ffff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gggg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hhh] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iii] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jjj] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[kkk] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lll] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mmm] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nnnn] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ooo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ppp] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qqqq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rrrr] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssss] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tttt] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuuuu] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vvvvv] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wwwww] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xxxxx] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yyyyy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zzzzz] [int] NULL ,
[abc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[def] [datetime] NULL ,
[ghi] [datetime] NOT NULL ,
[jkl] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mno] [bigint] NULL
) ON [PRIMARY]
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.
Thanks.
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.
View 2 Replies View RelatedI 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.
Mike B
Hi,
Is there a way to copy and save my Design Table? I want to transfer it to another workstation.
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
Thanks
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
I create following tables.
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
employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint
COUNTRY_NAMEvarchar
ie. during data entries direct insert the country_name in employee
table ?
I want to create separate table for separate data.
department,postion,country.
How I can use unique country_code in employee table's two
fields ?
regards
Martin
Hi,
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?
Cheers,
Matt
Hi,
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.
Thx.
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?
Thank you
Hi All,
If I have the following data that I need to put in a lookup table, what would be considered the proper create table schema for it?
G, "Group"
R, "Region"
D, "District"
Would you setup your table like this:
Level_ID Varchar(1),
Level_Desc Varchar(20)
Or would you go ahead and create like this
Level_ID Integer,
Level_Code Varchar(1),
Level_Desc Varchar(20)
Not sure which one would be the preferred method.
Thanks,
JJ
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..
So I put a workaround
Table - MatchPlayer1
-----------------------------
Columns -
MatchPlayerID (Primary)
MatchID (Foreign to "MatchD" in Table "Match")
Team1PlayerNames (Comma Delimited values - varchar(800))
Team2PlayerNames (Comma Delimited values - varchar(800))
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.
Thanks in advance
hi there,im new to sql server 2005 and this forum
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
thanx in advance!
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,
hi,
i have a table with data in the following format (dont ask why it was done this way, its not something i can change)
so my table looks like this:
point :timestamp :value
_________________________
var1 11:00 5
var2 11:00 2.3
var3 11:00 21
var1 12:00 5.2
var2 12:00 2.4
var3 12:00 18
and so on.
i would like to have my data rearranged to look like the following:
timestamp :var1 value :var2 value :var3 value
________________________________________________
11:00 x y z
12:00 a b c
....
i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.
thanks guys.
i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before.
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos.
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.