Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback.

Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution.

Here is the table design I have so farhttp://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list...

Thoughts please. TIA

I would suggest the following as it pertains to the business logic of your task.

Create a table for the "week" with weekId and date (no time reqd). This is a central table that links most of the others together

Create a table formoney input or "Subscription" with a link to your dateId and user

You have a table formoney output - winnings which links to dateId.

You might also need to store more user details - maybe more on address, more phone numbers etc.

|||You are in the right direction about your table design while the previous advice not about table design, you can use Microsoft provided membership for your users and just add DRI(declarative referential integrity) to team, schedules and subscription fee tables, money you need to use Decimal or Numeric because you can set precision and scale. Hope this helps.

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:
> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>sql

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have key
s
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIA
Well, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Base Database implementation

I am currently looking to buy or build software that enables my firm to:

Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.

The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.

What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?

We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.The aPress book Real-World SQL-DMO for SQL-Server, by Mitchell & Allison, describes (and includes source on CD) the VB code to perform this very task. You would create your "empty" database once. Then, run the tool against the database to script, not only the database structure, but the data as well.

Then, just run the script on a new server to create your database.|||Thanks I just purchased that book based on your advice, we had a meeting to further discuss, this is what we came up with:

I. Structural
We agreed using a tool like Erwin is the best approach, have 1 point person (with a backup in place) that would own the database. When development teams are ready to merge up they would tell the point person which DB their project uses. The point person would then use Erwin to merge their structural changes into the master data model. Once that is complete, scripts can easily be generated to serve as master install scripts for new projects into empty DBs.

II. Functional
Currently we use subversion to store our views, UDFs and stored procedures. Regarding this piece we discussed creating a simple program to manage the creation of these scripts using the individual Subversion files as the repository. I purchased a book that comes with a CD that has VB code that utilizes the SQL DMO in order to create the necessary scripts. Ideally we would want an application that takes an INI type file with a list of objects and generates the necessary files to create those objects. This could be used in two situations the first would serve as a solution to the base DB install for UDFs, Views and Stored Procs. The second situation would be to aid in managing changes in the Development environment when we need to push changes to QA in the form of install scripts. Currently we have a very tedious process of generating the necessary SQL per object and having to find the object in our original install script, delete what is in the script and paste our new code into the script. By having an application in place to automate this process would be a great aid in generating the Base DB scripts and even more so with regards to ongoing developing.

III. Data
Depending on how the Functional phase described above goes (the aforementioned book also has code to perform similar actions on data) we would look to apply similar functionality to seed the Data. As an alternative we could look at automation of BCP file creation. There are two types of data mandatory lookup data that would also be used for QA / Production releases, and non-mandatory data, i.e. seed data, that would be used to improve basic functionality of the 3 sites.

IV. Test Scripts
This portion would need to be revisited when the time is appropriate I would like to look at solutions that would help in automating test scripts. Ideally the purpose of this phase would be to ensure all three sites are working post-merge. The easiest way to ensure proper functionality is to run automated test scripts that can return a PASS or FAIL result, rather then for each project having to manually follow a test plan.

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:

> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote: