Hey guys. I'm new to database programming, been reading books lately and have created several desktop applications. I am creating a website in ASP.NET(C#). I want(I think I want) this website to be database-driven as it will hold tons of user information.
Without explaining too many details of my idea for the website I will explain one part of it in enough detail so you guys can understand my questions.
I have one database file that contains MANY tables but the ones to note are the following tables:
"Users"
"NFLWeek1"
"NFLWeek1UserPicks"
The "Users" table contains all the users' information. Column one being "User_ID"(Primary key)
"NFLWeek1" lists all(16) games in week 1(row 1 being game 1, row 2 being game 2, etc) - the columns for this table include; team1name, team2name, team1score, team2score, winner, etc.
"NFLWeek1UserPicks" list all the users' picks for each game in that week. Each row being a different user. Columns for this table include; User_ID(foreign key), Winnergame1, game1wage, and several other columns pertaining to game1, THEN Winnergame2, game2wage, etc. etc.... which means this one table is 96 columns wide! That's for only 16 NFL games in a week. If I did the same for MLB, having roughly 100 games a week, my "MLBWeek1UserPicks" table will be roughly 600 columns wide!!
So here's my question(assuming I haven't bored you yet)... Am I going about this the wrong way??? Would there be a better way of saving this user information??? It just seems to me that this is a clumsy way of storing this information. But I don't have enough database experience to know. Thanks for any input on this guys!! Cheers!
You should look at the built in framework login contro. it will require you to create a separate DB for user's and what not. this will give you a layer of separation and more finite control over login access. Then for your other stuff, yes have it all in one database. The database design is up to you, everyone has their own way of doing it and I've seen well experienced DBA's create them in different fashions. Without knowing all the 'tid-bits' of your database, it will be hard to construct. You will find yourself potentially restructuring your database many times, because even you, with all the knowledge of what you want, will not get it right the first time; no one does. That is why code is changed and updated.
As far as setting up the database and all that fun stuff you are better off choosing a database, and then requesting help there. While you may be doing an asp.net application, it has nothing to do with the database. From the .net side all you will care about is how to connect, and populate and all the other CRUD stuff.
it will require you to create a separate DB for user's and what not. this will give you a layer of separation and more finite control over login access. Then for your other stuff, yes have it all in one database.
This is actually a poor design, if you are looking for referential integrity.
That is, and for example only, if you don't want to able to delete a given user if there are any record s in other tables that are referencing his user id.
At a minimum, I'd want the two TABLES in the same database, so I can make cross table references easily.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
from my understanding though, with the asp.net login control - and built in security features- (you have to run a database configuration) it set's it up and makes it unable to use as a regular db then... I could be very wrong on this as I'm not a DBA- do you know?
It's been a long time, but you *used* to be able to configure the login stuff so it would use your own database and table instead of creating a new one.
Before you post, read our: 
