| Author |
Topic |
|
christina_rules
Starting Member
23 Posts |
 Posted - 2007-10-19 : 02:23:36
|
| hi,i'm wondering if there's a possibility for me to insert values into a table without knowing fix number of fields? in other words, i won't know how many fields i have in a single table until users enter data.any suggestions or recommendations are welcome. thanks in advance. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-19 : 05:51:19
|
| How can you not know this?? Unstructured data is usually stored in a text/image-datatype along with some information to identify each record. You need to exlain a little more and give relevant examples to be able to get some real help.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-19 : 10:04:13
|
if you explicitly state the columns in the insert statement, you can do this, as long as all other columns allow null:insert MyTable (col1,col2,col3) -- specify column list hereselect 1,2,3
elsasoft.org |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-19 : 10:24:21
|
quote:
Originally posted by christina_rulesin other words, i won't know how many fields i have in a single table until users enter data.
Why wouldn't you know until they enter data? You can always type exec sp_help 'yourtable' to figure that out.
Future guru in the making. |
|
|
|
christina_rules
Starting Member
23 Posts |
Posted - 2007-10-22 : 22:12:23
|
| okay what i want to do is this..for example, i have a table that stores all the types of pencil used by a person. however, a person can use different brands or models of pencils right? in my form, i allow users to enter however much pencil brands or models they want. the thing is, i wouldn't know what brand or model they use until they enter their data. one person may enter 30 types of pencil while the other person may enter 10 types. can this be done? i want to read all the pencil records from a single table that is tied to that one user. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-10-22 : 23:00:44
|
Then you should have the brand as a column of your table and not 10 columns or 30 columns.something like thiscreate table table1( user_id varchar(50), brand varchar(10)) How does your table looks like ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
christina_rules
Starting Member
23 Posts |
Posted - 2007-10-23 : 02:24:05
|
quote:
Originally posted by khtan
Then you should have the brand as a column of your table and not 10 columns or 30 columns.something like thiscreate table table1( user_id varchar(50), brand varchar(10)) How does your table looks like ?
KH[spoiler]Time is always against us[/spoiler]
well, right now i fixed 10 columns naming then one, two, three til ten. so, whenever users enter their brand, i just insert it inside these ten columns. but i also have to cater for the possibility of users entering more than 10 brands.may i know what you mean by just creating 'brand' this column? thanks. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-23 : 09:53:16
|
the idea is, if you have a user with 10 brands, you create 10 rows in the table. this is a much better design, as it can support a single user with 10 million pencil brands and another with just a few in the same table.
elsasoft.org |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 11:31:16
|
| Yep. You need to totally redesign your table(s). Do it now. Otherwise it will be exponetialy more difficult later. |
|
|
|
christina_rules
Starting Member
23 Posts |
Posted - 2007-10-23 : 20:24:09
|
| okay. i'll redesign my table. thanks for all your inputs. :) |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-24 : 09:36:58
|
| The idea is kinda like this (I'll probably be corrected by the guru's here):You have a Users table and a Brand table.Users:UserIDNameOther demographic data/fieldsBrandBrandIDBrandNameOther Brand data/fieldsThen you have a User_Brand table which has only the fields UserID and BrandID. Say my UserID is 10 and the Brands I use correspond to BrandID 5, 7, 11, 23, and 41 (in other words, I use 5 Brands). Here's what the User_Brand table would look like for me:UserID BrandID10 510 710 1110 2310 41As you can see, I can basically have as many Brands as I want. There are no structure changes (adding fields) to any tables. The User_Brand table will grow vertically instead of horizontally (your current table design). If you were to keep adding fields as you stated before, everytime you add a field (brand) then every report, query, ect has to be changed that uses that table and calculates on brand(s). The way I suggested...well you have no table or code changes to make once you have it set up. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
how to deal with no fix number of fields?