Survey DB Schema
Dec 12, 2004
I am trying to modify a survey DB schema created by OOP(Object Oriented Programming) snobs who had their heads so far up their butts(in my opinion).
This is current schema created by them.
Diagram 1 (OOP snobs version)
http://us.f3.yahoofs.com/users/41a4fde6zcff655d9/a2ae/__sr_/2761.jpg?phsEJvBB3t1v7URD
Actually there aren't a several foreign keys as they are shown in the picture, but that's what they(OOP snobs) are doing to the table "Response". They are inserting question_collection_id and its name as well as question_id and its name to Response table. However, it was not ill-intended, "We just wanted to load the report quickly"(OOP snobs).
But I really recommend this schema, Because of its simplicity.
Diagram 2 (My version)
http://us.f3.yahoofs.com/users/41a4fde6zcff655d9/a2ae/__sr_/e890.jpg?phrEJvBBF0.lGY5Y
Yet I haven't proven that my schema will provide report just as quickly as OOP snobs' version. As mentioned earlier, since they don't have foreign key constraints accordingly, I do find quite number of discrepancy on values of "question" columns between "Question" and "Response" tables and "question_collection" columns between "question_collection" and "Response" table. I can provide schedule task to eliminate those problems. But I really prefer my schema than theirs. So far, I can explain mine is better just because of its simplicity and DBA's gut instinct. I'd like to hear Superior Expert's opinion which schema is better and why one is better than the other.
Attached is also zip files of schema images if you can't see them by those URL.
View 4 Replies
ADVERTISEMENT
Nov 27, 2007
SQL Server Integration Services team would like to hear from you. Our goal is to understand your current Data Extract, Transform & Load (ETL) practices and prioritize features for future releases.
You will have a chance to win one of five 80GB Zunes. To win a Zune, you must fill out the survey by Dec. 20, 2007.
To participate in the survey, please click here: https://mscuillume.smdisp.net/Collector/Survey.ashx?Name=SqlETLSurvey2
Thank you,
SSIS Team
View 2 Replies
View Related
Jun 16, 2007
The BI Survey is the largest independent survey of BI technology users worldwide. (It used to be known as the OLAP survey, but has expanded to cover a broader array of BI technologies.) The survey analyzes people's buying decisions, BI implementation cycles and business results achieved.
The BI Survey is strictly independent --- they ask vendors like us to ask our customers like you to participate, but we don't have any other involvement --- except closely reading the results, of course. The Survey will send all participants a summary of the full survey results and place them in a drawing for ten $50 Amazon vouchers.
You can complete the survey on-line by following these links:
English version - website: http://www.eu-survey.com/webprod/cgi-bin/askiaext.dll?Action=StartSurvey&T1=BI&lan=1&lin=20
German version: http://www.eu-survey.com/webprod/cgi-bin/askiaext.dll?Action=StartSurvey&T1=BI&lan=2&lin=67
Let your voice be heard!
View 2 Replies
View Related
Aug 29, 2006
I am going into salary negotiationyearly review next month after my week off and I am trying to determine what to ask for. I am thinking another 10K and an extra week off would not be unreasonable, but I wanted to get some idea of what you guys think I should ask for in terms of pay.I have been developing software for seven years and I have been a dba for a little over five. I live in pricey northern Virginia. On a fairly regular basis I do about 55 to 60 hour weeks. On my DBA team, I am the only one who can handle both development and production tasks. The others are strictly developers. Although I have been relieved of most of my customer support tasks by our newbie, the customer support manager still brings the nastier bits to me. It is my perception that the more complex tasks get assigned to me. I get told on a regular basis that I am the best dba this place has ever had and other embarrassing accolades are regularly thrown my way. After a year, other than my boss I am the dba that has been here the longest in high turnover high burnout company. This year as we try to move to a SAP model, it looks like we will be going to 24/7 support on a disaster recovery model I am designing and implementing, so I guess I am getting the pager.So how much money should I be asking for?you can PM me with a number if you want.
View 1 Replies
View Related
Feb 5, 2007
Is there a possibility to express the text column with SELECT SQL command, to get AS column which will represent all values in the field?
I mean - to convert:
IDresult__IDquestion_answers
....
24______72_________178,180,191
25______77_________221,224
26______79_________291,298,301,302
27______84_________522,523,525
....
into:
IDresult__IDquestion_numberedanswers
....
24______72_________178
24______72_________180
24______72_________191
25______77_________221
25______77_________224
26______79_________291
26______79_________298
26______79_________301
26______79_________302
27______84_________522
27______84_________523
27______84_________525
....
Can SQL do that?
View 14 Replies
View Related
Oct 16, 2007
I have surveys that I need to add weights to and was wondering if there was a way to convert the contents of a column.
select empid, ans_for_ql,
(if ans_for_ql = A then 0, B then 3, C then 5) as weightscore,
ans_for_q2,
(if ans_for_q2 = A then 8, B then 4, C then 2, D then 1, E then 0) as weightscore
Here is what the table looks like:
empid | Ans_for_Q1 | Ans_for_Q2
1001 A C
1002 B E
And these are the possible answers and what they need to be converted to:
Weights for answers to Question1
Q1_A = 0
Q1_B = 3
Q1_C = 5
Weights for answers to Question2
Q2_A = 8
Q2_B = 4
Q2_C = 2
Q2_D = 1
Q2_E = 0
View 3 Replies
View Related
Feb 5, 2008
hi,
i'm having following data:
create table survey
(
clientID int not null,
questionID int not null,
Answer int not null
)
insert into survey (clientID, questionID, Answer) values (1, 1, 1)
insert into survey (clientID, questionID, Answer) values (1, 1, 2)
insert into survey (clientID, questionID, Answer) values (1, 1, 4)
insert into survey (clientID, questionID, Answer) values (1, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 1)
insert into survey (clientID, questionID, Answer) values (2, 1, 5)
insert into survey (clientID, questionID, Answer) values (2, 1, 8)
insert into survey (clientID, questionID, Answer) values (3, 1, 2)
insert into survey (clientID, questionID, Answer) values (3, 1, 4)
insert into survey (clientID, questionID, Answer) values (3, 1, 6)
insert into survey (clientID, questionID, Answer) values (3, 1, 9)
insert into survey (clientID, questionID, Answer) values (4, 1, 8)
insert into survey (clientID, questionID, Answer) values (5, 1, 1)
insert into survey (clientID, questionID, Answer) values (5, 1, 5)
insert into survey (clientID, questionID, Answer) values (5, 1, 9)
and i want to get it out like this:
ClientIDQ1_1Q1_2Q1_3Q1_4
----------------------------------------
1|1|2|4|5|
2|1|5|8|0|
3|2|4|6|9|
4|8|0|0|0|
5|1|5|9|0|
but i get the following output:
clientID Q1_1 Q1_2 Q1_3 Q1_4
----------- ----------- ----------- ----------- -----------
1 12 12 12 12
2 14 14 14 14
3 21 21 21 21
4 8 8 8 8
5 15 15 15 15
(5 row(s) affected)
with this code:
SELECT
distinct(clientID)
,Q1_1
,Q1_2
,Q1_3
,Q1_4
from
(select
distinct(clientID)
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_1
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_2
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_3
,sum(case when QuestionID = 1 and Answer in (1,2,3,4,5,6,7,8,9,10) then Answer else 0 end) as Q1_4
from
dbo.survey
group by clientid) as x
group by
x.clientID
,x.Q1_1
,x.Q1_2
,x.Q1_3
,x.Q1_4
order by clientID asc
what am i doing wrong? Thank you
View 9 Replies
View Related
Jul 23, 2005
Hi,We are a systems research group at the Computer Science departmentatRutgers University, and are conducting a survey to understand detailsabout network, systems and database administration. We hope that thisinformation would help us recreate a realistic environment to helpresearch in 'systems management'.We request network, systems, and database administrators to take thissurvey. As an incentive, all surveys completed in their entirety willbeentered into a drawing of a number of $50 gift certificates (fromAmazon.com).We hope you have a few minutes to take the survey which is located at:http://vivo.cs.rutgers.edu/administration_survey.htmlResearch in our group:The goal of our research is to improve the overall availability andmaintainability of services. Since administrators form an integral partof these services, a key aspect of this work is to build environmentsand tools that ease the task of service administration. In particular,environments which would help administrators know how their actionsmight impact the real service (before performing them for real), webelieve would be useful in preventing inadvertent actions. This surveytries to understand the existing environments, what administrators docurrently to test the 'validity' of their actions, and the difficultiesthey face in doing so. The two specific systems we are looking at arenetworks and databases, as we believe these are important components ofmany services.If you have any questions regarding this survey or our work, feel freeto email us:Fabio Oliveira (fabiool at cs || rutgers || edu), orKiran Nagaraja (knagaraj at cs || rutgers || edu)Thanks for your time,Fabio OliveiraPhD student, Vivo Research Group (http://vivo.cs.rutgers.edu)Rutgers University
View 4 Replies
View Related
Jul 23, 2005
Dear Madam/Sir,You are invited to participate in an international research study. Thisresearch project is headed and led by Cambridge student Nico Baumgarten.What is it all about?It is a well-known fact that there exist many differences between cultures.However, how these differences effect motivation if there are effects atall is not yet clear. This survey is set out to change this. With yourhelp, it will be possible to achieve this.Therefore, we kindly ask you to spare only 10 minutes of your time andvisit the survey home page.Go to the survey http://crosscultural.research.yi.orgor if this does not work the direct link http://82.4.146.32Who participates?All over the world, people are chosen to participate in the survey.Although the survey is completely anonymous, each person is arepresentative of his/her country. Hence, it does not matter, which job aperson has, whether he/she is male or female, old or young. The more peoplechoose to participate the more accurate this survey is going to representand show cultural influences as well as similarities.So if you want to tell your friends about this survey - Please Do So! Justforward this email to your friends or use the feature on the survey homepage.What is in it for you?With your help your country will be represented in the survey.Of course, you can opt-in to receive the results of the study at the end ofthe survey.Any information you provide will be kept strictly confidential andanonymous.Thanking you in advance for you support I remain withKind regardsNico Baumgartenhttp://crosscultural.research.yi.orghttp://82.4.146.32
View 1 Replies
View Related
Aug 26, 2005
I am a developer and I have a problem trying to design a system tomanage data coming from web surveys. Each section can potentially havedozens of questions, i.e., fields.I am focusing here only on the table(s) that will hold the survey data.I do not have any DDL as I am still trying to understand this!All the examples I have found so far in books and on the web seem todeal with fairly limited data, that is easily, or so it looks, brokendown in multiple tables.It seems that, from my research, having a wide table per survey sectionwith each field as a column, which has been suggested to me, is notproper design for many reasons - missing values for non-requiredquestions, table with 100s of possible columns, etc... - so I playedwith the idea of a single table where one of the columns would be theforeign key pointing to a questions table and another column would holdthe data (this is a simplified explanation.)The problem with this is that now this column will have to accomodateall types of data, from bits to large varchars, and that fieldvalidation seems now impossible jeopardizing data integrity.What do knowledgeable designers come up with in this case? Can someonepoint me in the right direction?Jerry
View 2 Replies
View Related
Jul 23, 2005
I've written several survey systems in which the majority of the questionshave the same or similar responses (Yes/No, True/False, scale of 1 - 5,etc).But this latest survey system I'm working on has 8-10 sections, with avariety of question attributes and answer scales. Some items have just adescription and require a Yes/No answer, others have a description and anactive status and require a Yes/No and price answer, some require a comment,etc.Rather than build a separate response table for each survey section, I wasthinking of building one generic response table, and trying to force allsections to fit by adding columns - some of which won't apply to some items.Like this:Survey Category (will apply to all items)Survey Section (will apply to all items)Item Description (will apply to all items)Item YN (will apply to all items)Item Price (will apply to about 10% of the items)Item Points (will apply to about 10% of the items)Item Active YN (will apply to about 10% of the items)Item Fail YN (will apply to about 10% of the items)Item Comment (will apply to about 10% of the items)For instance, in the structure above the field "Item YN" would representmultiple types of answers: is the item in use?, is the item in place?, isthe item given away for free?, is the item on display?, etc. Basically,anywhere a Yes/No answer is used.The advantage is one source table (rather than 8) for storing answers, andit might be easier to query and report on.The disadvantages I see are 1) it's more difficult to understand the meaningof the responses when the answer field is named Item YN, and 2) you have anon-normalized table that's difficult for a 3rd party to understand.If I have the questions and responses in separate tables, I'll use nameslike "ItemComplimentaryYN" and "ItemUsedYN" depending on the question. It'seasier for others to learn the data.I actually don't like the "generic" approach, and probably won't use it, butI figured I'd try to get some input from others who've written surveysystems.Thanks
View 5 Replies
View Related
Oct 15, 2005
Dear friendsI am conducting a survey on Relational Database usage and would likeyour help. The study is part of my MBA Dissertation.Could you kindly spare 5 minutes to take part in this survey?http://FreeOnlineSurveys.com/rendersurvey.asp?id=120816ThanksRajeev
View 11 Replies
View Related
Apr 23, 2008
I am working on a survey system with another DB Engineer.Our current design includes 1 table per Question Type (Numeric, Boolean, Text), Response values are tightly coupled based on data being entered, to keep things simple assume there are only 6 tables in this this system (1 for each Question Type to define the "rules" of the data entered, and 1 for each Response collection).He is a former .Net programmer and has been talking with the .Net guys on the project and he is proposing that we abstract our Question Types to use one table and store the Input Values all as nvarchar(max).This obviously simplifies the DB design and reduces the amount of work I will need to do so I should be all for it. I guess I am a little concerned that we are no longer tightly coupling the data types and essentially leaving all the validation up to the application. Also should I be worried that a bunch of bit data will be stored as 1/0 in an nvarchar(max) column?Thanks,-John
View 13 Replies
View Related
Mar 23, 2015
I have questions table and answers in survey database. Now I design responses table from users !
CREATE TABLE [dbo].[Response](
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[QuestionId] [int] NULL,
[AnswerId] [int] NULL,
[VoterId] [int] NULL,
[Code] ...
My Questions : How can I store data if questions is multiply answers (checkbox type) ?What is best way ?
View 16 Replies
View Related
Aug 5, 2006
Several prominent researchers are conducting a survey and looking for500 respondents by Friday on the topic of enterprise architecture andsoftware development. Here is the link to the survey:http://www.surveymonkey.com/s.asp?u=845572436515After completing the survey, results are displayed immediately. A smalldonation will be made to a charitable organization after itscompletion.http://duckdown.blogspot.com/
View 3 Replies
View Related
Dec 20, 2007
I am a developer and I have a problem trying to design a system to
manage data coming from web surveys. On average each subject will take
part in more than one survey and each survey may potentially have 100+
questions.
I am focusing here only on the table(s) that will hold the survey
data.
I'm thinking about having a wide table storing each question in the
survey(s) as a column against the subjects. My main concerns with this
approach:
- the "yuckiness" and potentially other performance issues(?)
associated with a table with 100s of columns
- the 8kb size limit per row (unlikely to touch it, but possible)
- 1024 column limit (unlikely to touch it, but possible)
Another approach obviously is to have a single table where we have, in
a simplified version, 3 columns: person_id, question_id and data/
answer. The problem with this is that the "data" column will have to
accommodate
all types of data, from bits to varchars, and that field validation
seems now impossible jeopardizing data integrity, and most
importantly, you can't easily work with the data in filtering/
reporting etc.
What do knowledgeable designers come up with in this case? Can someone
point me in the right direction?
View 2 Replies
View Related
Jul 24, 2015
Basically our cube contains two different Facts:
- Sales Volume: Information about sold articles to a customer incl. Selling date
- Survey: irregular answered survey questions about customers incl. date of answer
and three Dimensions:
- Customer
- Date
- Survey Answer: Information about possible Answer values (e.g. Yes / No)
Relations:
Fact: Sales Volume ------> Dim: Customer <------ Fact: Survey ------> Dim: Survey Answer
'--------------> Dim: Date <----------------'
We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.
For example:
Selected Time period: Jan - Jul 2015
Sales Volume Customer X - Jan - Jul 2015: 1000 Litres
Sales Volume Customer Y - Jan - Jul 2015: 500 Litres
Surveys answered:
15th Jan 15: Customer X, Survey Question A: Yes
2nd Mar 15: Customer X, Survey Question A: No
20th Apr 15: Customer X, Survey Question A: Yes
10th Feb 15: Customer Y, Survey Question A: Yes
20th Jul 15: Customer Y, Survey Question A: No
Latest survey answer (Jan-Jul) Customer X, Question A: Yes
Latest survey answer (Jan-Jul) Customer Y, Question A: No
Excel Pivot should show something like this:
Question | Latest Answer: Yes | No |
------------------------------------------------------------------------------------
A | 1000 Litres | 500 Litres |
How this is possible and has to be implemented?
View 5 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Apr 19, 2006
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies
View Related
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Jun 25, 2001
SQL 7.0 SP3 replicating a database from a publisher to a SQL 7.0 SP 3 subscriber.
Is there a way to change publisher schema without stopping the replication? Example Add a 'bit' column to a table with default of '0'.
View 1 Replies
View Related
Jun 3, 2004
Hi all,
I want to set up a db for an e-commerce site. I need to know how to set up the db correctly with out getting anything mixed up ... 'cos I never done an e-commerce db b4:
I need to know where and how to store the passwords, the product pictures and the customers delivery address which is different from the billing address.
The tables are as follows...
--Customer--
CustID
Email (email & pwd is for login)
password (will this be secure here)
Name
Address (should it all be in one column or firstline,secondline,zipcode columns)
DeliveryAddress
--Product--
ProductID
ItemName
Catogary
Price
SellingPrice
Quantity
ItemsPicture...(not sure where to link the pics to)
DistributorsID (the warehouse who dispatches the item)
--Distributors--
DistributorsID
Name
Company
Address
It would be much appreciated if you could share some info and tips for how to set it up all correctly.
Tools I'll be using are, ASP.NET(C#), ADO.NET, MSSQL (Stored procedures)
Thanks In Advance
View 4 Replies
View Related
May 28, 2007
HI All,
What Is Schema in sql Server 2005 ?
View 4 Replies
View Related
Nov 23, 2007
Good afternoon
could someone tell what is a schema is when using within SQL SERVER 2005
Many thanks
Rob
View 3 Replies
View Related
Aug 26, 2005
I know in SQL Server the terms Database and Catalog are usedinterchangably. But a table is also assigned a schema. As seen in theINFORMATION_SCHEMA.Tables View. I don't get what this schema qualifieris all about. Like if a table has a schema of dbo.Can someone explain the relationship the schema has and what it is?Thanks.
View 10 Replies
View Related
Nov 8, 2006
I have setup merge replication between two machines and the data seems to be updating when changed on either side ok. However I am now looking at how I can handle schema changes. I have found on msdn (although not tried) stored procedures that will add and remove columns, but what happens if I want to delete a table for example.
At the moment I can deselect the table being deleted from being published and then delete it or modify it in Management Studio, which then means I need to create a new snapshot which can take sometime before the change is updated on the subscriber.
Is there a better way to tackle schema updates that I have not found yet? Also, how will the subscriber cope if someone starts modifying data while synchronisation to the new snapshot is going on.
I am using SQL 2005 by the way.
Thanks
John
View 1 Replies
View Related
Oct 17, 2006
Hi,
I am new to SQL Server and started it with SQL Server Express.
I tried unfruitful to know about 'Schema' in SQL Server.
I have to mention it as prefix for table name in SQL query. But if it is in "dbo" schema, it is not required.
Please tell me about it..
Thanks
View 1 Replies
View Related
Nov 19, 2006
I wrote a website that hits an SQL Server DB using queries that directly acces the table name with no Schema references. (Ex: SELECT * FROM tblHelpNotes;) I have also backed the file and and somehow (I have forgotten how I managed to import it.) imported it into SQL Server Express to run at home. The problem I am having is that in order to query at home I HAVE to use the Schema or it won't work. (Ex: SELECT * FROM dbo123456.tblHelpNotes;) How can I change this so I can just hit the table directly? I have tried miserable to set up a user that uses the default schema of db123456 but I can't seem to get it to work. Any idea's to point me in the right direction?
View 2 Replies
View Related
Jun 19, 2000
Hi,
i need to move the data and schema from 6.5 to 7.0. could you please tell me the most simple way.
thank you
raju
View 1 Replies
View Related
Dec 27, 2004
Hi,
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Thanks
View 4 Replies
View Related
