Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bohoirup
Starting Member
3 Posts |
 Posted - 2014-07-09 : 08:34:27
|
| Hi allHoping for a little help on this question. If i have a list of fields ex. (name,address,postal,phone etc.). Then i create a webform/task to gather some of theese fields (name, postal), then i make another webform/task to gather some other fields (address, phone). What is best practice in the SQL server for storing returning values.Is it: 1. to make a table with all the fields in the list + taskid. Theese fields could be in correct format (number, date etc.). And all answers to all tasks is inserted into this table. 2. Make a value table for each field with the correct type + task id. So all name values are stored in the "name value table" with the task id.How would i select values from a certain task from this kind of setup?3. ??Best regardsBo |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-07-09 : 11:13:55
|
| You want the field names to be the same in the SQL table and add a primary key of TaskID, make sure you get the correct datatypes for each field, datetime, varchar(50)...you need to work out if you need a person table and other such as contact detailsWe are the creators of our own reality! |
 |
|
|
bohoirup
Starting Member
3 Posts |
Posted - 2014-07-10 : 03:09:09
|
| Thanks for the reply, but the fieldnames was just an example. Say instead that it is 50 fields in a table with their own unique ID, maybe an answer table would look like this:taskid | field_1 | field_2 | field_3 | field 4 | field_nSo no matter which fields the user fillsout it will can be stored in one table. QUestion is, is this a good way to do it? and how do i select from this table using a join ?As far as i know you cant name columns in a table with just numbers, which would have been great, giving the columnnames the field_id.Best regardsBo |
|
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-07-10 : 05:58:38
|
| Hard to tell without the structure but if you are talking about the numbers are all unique then why cant you have them as the primary key field, soUniqueID, Type, additional columns...1234 wood2345 metal3456 leather4567 plasticThat way your ID will always be unique and the primary keyReally it depends on how much data you have as to how many tables and relationships you will need, a quick data set can be used on one table although in SQL terms its not best practice but if you have small dataset then it would suffice. Although later down the road you may want to add additional tables..you dont need to join on a single table unless you need to do a self join.We are the creators of our own reality! |
|
|
|
bohoirup
Starting Member
3 Posts |
Posted - 2014-07-11 : 03:37:18
|
| Can i try to explain my scenario again, may have found a better way to describe the setup.Table1 contains 30 rows, each row containing a field_id (unique) and a field_type (ex. datetime, float etc.)I build a webapp that displays random 10 of the 30 fields from Table1 and ask the user to fill out the form. What i would like to do is make sure that each field the user fills out is stored in the correct datatype in the database, and also that it is easy to select any given answer by one or more users, selecting only the filled fields. I am not sure this can be done without using dynamic SQL?And the big question is, should i have one answer_table containing all 30 field_id + types, inserting each answer (1 row) in this ORShould i create 30 answer_Tables each representing a field_id from Table1, and storing the answer across theese tables. And furthermore how do i select any given answer from this type of data structure?Hope it makes a little more sense this way, or have i just explained the same scenario again? :)Best regardsBo |
|
|
|
|
|
|
|
|
Best practice for saving data in SQL server