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 |
|
wixxard
Starting Member
3 Posts |
 Posted - 2007-05-01 : 23:18:50
|
| I've fought with this issue for a week now and I can't come up with a simple way of doing it.I have a database, with the following tables:Members MainCategories CategoriesAnswersThe idea is that Members will log into the site, select the Categories from the MainCategories, fill out some data, which is stored in the Answers table. The problem is, each of the main categories have different fields. So a Main Category might be "Shoes" and the fields would be "Color,Size,Brand", and another Main Category might be "Cars" and the fields "Make,Model,Year,Color,Type,IsNew".I thought about creating the Answers table with all the possible answers, linking it to the categories and members table, so we can view which members filled out answers for which categories.Can anyone help me come up with a solution for this? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-05-02 : 02:13:07
|
| You've described the structure but not the entire problem.a) What do you collect (stuff about stuff)b) What do you want to do with itc) Volume (this can make a difference - there's a crap data model to do this but it is sucks for performance when it gets too big and is shitful to do complex queries on)d) What reports do you need to get out?e) What analysis are you going to want to do on the data? f) Are the categories in any way related (do you want all things red or just red cars?)g) How flexible are the categories etc - how often do you want to add new ones?h) Is it only text data or numeric/date too?etc. etc.The answers to these and others will help decide the best data model. |
 |
|
|
wixxard
Starting Member
3 Posts |
Posted - 2007-05-02 : 20:36:38
|
| a) The members will answer the questions. For instance, Shoes they may answer White, 10.5, Nike. b) Other members or admin will view the answers, so I'd need to select all their answers for each category.c) Initially small, but may grow in the years to come. Perhaps 100 users, with 10 main categories that all have 3-7 fields.d) No real reporting is necessary other than what appears in B.e) No analysis requirement has been set.f) Main categories are unique, but the categories may appear in more than one. The main category fields will definately be reused.g) Adding won't happen often, if ever. I hope deleting a category never happens, it's certainly very unlikely.h) All input formats can be expected.Thanks |
|
|
|
wixxard
Starting Member
3 Posts |
Posted - 2007-05-05 : 16:04:46
|
| anyone? any help? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-05-06 : 21:59:26
|
| I would have a members table and then one table for each main category with a column for each answer.As far as I can see that's all you need.I can sort of see where you're going with the other tables. You might want some meta-data related to each main category to help specify the questions in a programmatic style, so yes, your categories table would simply have a relation to the main category table and some stuff around it (description, type etc). If you want to share categories with main catagories then put a mapping table in between, but I probably wouldn't bother based upon what you've said.You can use this meta data to generate your stored procedures and maybe even a bit of your front end.Given the tiny size of the database, you could try the horrible super generic approach where the answer table contains a member ID, category ID and the answer the user gave. |
|
|
|
|
|
|
|
|
Design Help, please...