how to go about a project

[sdtke212]

Here is my dilemma,

I am about to start a project using ColdFusion and SQL Server 2000. Right now,
there is a site does on the fly charting for different city statistics. That
all works great. Where I come in is that I have to develop a part of the site
where people who
do not know really anything about databases. I have to make a site that will
allow someone (not anyone) come in and update
the database with knew statistics. There are about 50+ tables and each has
anywhere from 10's to 100's of records already in them. Now when I do this, I
have to use stored procedures. I was thinking about how I was going to go about
doing it, but all I am coming up with is writing a stored procedure for every
table and then on the page, depending on what table they want to update, call
that sp. Now I am no where near an expert on either ColdFusion or stored
procedures, but I do know some things and I pick up on things relatively fast.
I'm not looking for a solution, but some ideas on what route I want to take, or
ways so that I do not have to write 50+ stored procedures. If you need any more
information or there is something I left out, ask away. I'll keep checking this
post.

Thanks in advance for everyone's help,

Ryan

[The ScareCrow]

Ryan,

Without knowing the complete structure of the database and relationships of
the tables, I can only give an overview.

I would assume that there would be a "main" table and then the other tables
would be related to this table via a forieng key. But some tables could be
related to related tables of the "main" table.

So I would also assume that when the user needs to update/insert into these
tables it would be associated to a single record.
So you need to give the user the functionality to select a record of updating.
Once the record is selected a query would return all information (whether
direct or related). This would then be displayed in a form. The user would
then update any/all fields and submit the form.

The "action" page that this form is submitted to would then call the stored
procedure to update the required tables.

Thus in acuallity you would only need one stored procedure. Of cause this is
all dependant upon the tables relationships in the database. But it will give
you a starting point and something to work on.

Ken

[sdtke212]

Ken,

I don't have access to the database right now but off the top of my head and
from what I remember, the person who orginally designed the database did not do
the best of jobs. I do not believe any of the tables are related to one
another, just 50+ tables in one database. There is also no updating of the
fields, just inserting new fields.

One of the ways I thought of going was through I believe a CFGRID. I would
query the database and have all or maybe the most recent 5 rows displayed in
the grid. Then allow the user the insert the information they needed and submit
the form.

Maybe I should mention before that I had this entire site done previously
using PHP. Although when doing it before, I could just write the queries right
in the code and the insert queries were generated retrieving the field names
into an array, and then looping through the array and through each loop,
appending a field name to the query. Worked great. However, I am not sure how
this would work using stored procedure's and from the little knowledge I have
about them, if it is even possible.

Thanks for the advice,

Ryan

[The ScareCrow]

I do not believe any of the tables are related to one another

I would really hope not.

Before I can advise you any further, you will need to examine the database and
determine how the tables are used in the actual application. If they are all
independant tables, then you will not have much choice but to develop a sp for
each table.

But if the tables are used in a single application then I would have to assume
that they are related to each other in some way, maybe not the correct way but
some way.

There is also no updating of the fields, just inserting new fields
If this is the case then all you will need is a form(s) to insert new records
into the tables. I would also assume that in the sp you will need to check if
the record already exists in the table and if so, do not insert it.

Ken