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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 DB Design New Application

Author  Topic 

alxtech
Yak Posting Veteran

66 Posts

Posted - 2013-05-29 : 23:04:36
Hello Forum,
I am in the process of designing the database of a new application that It will be a web application, language/framework not yet defined.
The application will:
register users into the system (personal information)
all users will have the option in the application to select/unselect 500+ values yes/no and that value will be store in the database for later retrival by the web application.

Question, the challenge on this application is to design the DB in a way that will be efficient for the future, Example, when the count of users gets to 1000 users, each user over 500 values in its record.

i dont know if i am making my self clear, but to sumarrize i will say that each user will have cards from 1 - 500 some cards will be on and some off, i need to save that data for each user in the database.

thank you for any suggestion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:44:17
few options
1. use EAV model ie a separate table which will have userid,property,setvalue which will store properties against set values as rows

2. Make 500 attributes as sparse columns and set 1 for true cases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alxtech
Yak Posting Veteran

66 Posts

Posted - 2013-05-30 : 10:58:20
Thanks, i will research your suggestions, i am not familiar with EAV.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-31 : 02:04:16
quote:
Originally posted by alxtech

Thanks, i will research your suggestions, i am not familiar with EAV.



See

http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -