I just set up user profiles using asp.net 3.5 using wvd. For each user I would like to store data that they will be updating every day. For example, every time they go for a run they will update time and distance. I intend to allow them to also look up their history of distance and time from any past date. My question is, what does the database schema usually look like for such a set up? Currently asp.net set up a db for me when I made user profiles. Do I just add an extra table for every user? Should there be one big table with all users data? How do I relate a user I'd to their specific data? Etc.... I have never done this before so any ideas on how this is usually done would be very helpful. Thank you.
+1
A:
One easy way is to just add a (one) table to aspnetdb database, this table will be used to track your running session data for all the user profiles. Name it RunnerSessions or something similar, add the following columns to the table:
- UserID (use this field to store the profile id from ASP.NET)
- SessionDate (use this field to store the run session date)
- SessionDistance (use this field to record the distance from that particular run session)
It is a good idea to make UserID + SessionDate fields as your primary key for the RunnerSessions table or create another field called SessionID and set it as autonumber.
To relate each profile to a particular record in the RunnerSessions table, you can store the HttpContext.Current.User.Identity.Name in the UserID field mentioned above.
Jimmy Chandra
2010-06-01 07:22:24
Why does it matter to have a primary key, if user ID is used to realte data to user? (Any good beginner reference? - SQL Server Express 2005)
Greg McNulty
2010-06-04 05:40:03
Primary key will give you: 1) a unique identifier for a particular record (in your case, it's the user id and when that user is running) or the session ID if you go that route instead. 2) an index to make search / query faster which generally is a good thing to have.
Jimmy Chandra
2010-06-04 09:31:43
@Jimmy: Please see - http://stackoverflow.com/questions/2987254/how-do-i-query-table-data-only-for-the-current-users-id-vwd-asp-net-3-5
Greg McNulty
2010-06-07 22:27:54
A:
You are asking for a relational database design. As relational database design is an art in itself and heavily depends on a lot of case specific conditions I will not be able to provide you with a ready-to-go schema.
As I never read any online tutorial about this I will also not post a link here. But you should be able to get started by searching for a relational database design (tutorial) in your favourite search engine.
Maybe someone knows about a good tutorial and can post a link?
scherand
2010-06-01 07:26:19