Database "staging" area

I currently have the capability to serve up my Product Knowledge form to my clients so they can update their information; but I do not want them to be committing that information to my production db until it has been confirmed for release by my content management team. Would the following idea I have present any problems?

I want to create two pages (duplicates of the page I currently use):

1) Client. Information is posted to a "staging" database.
2) Internal. Content team opens this page and it loads the data from the staging database with two buttons on the page. One to update the staging database, and the other to push the data to my production db.

Any problems with this, or is there a better way to do it? I feel that I should probably create a second app to achieve all this instead of putting it in my production app.

Please advise.It might be worth considering createing a "Pending" column in your existing database rather than creating a shadow database. If you need to handle updates of exisiting information, you can create some sort of sequence field (perhaps just a datestamp) and then use views to get the current record, or the current APPROVED record from the production database.|||One of the reasons I considered a shadow database was because of the size of the table and data entry page; plus it would be easier for me to replicate the db and app. It may seem like the lazy approach, but I have some serious time constraints. I was thinking I could just open the shadow db for editing by the client in one page, and the content management team could open another replicated page that has an extra button, and that button would push the data to the production db.

The aspx and code behind pages total 11,000 lines of code and the db has a couple hundred columns in it. And before I get hammered on normalization... "My boss made me do it!!!"|||::One of the reasons I considered a shadow database was because of the size of the table
::and data entry page

Both are ntot relevant, sadly. Thesize of the tabele should not be an isue. Using a staging databaese creates all kinds of problems though.

::plus it would be easier for me to replicate the db and app

Hm. So the app would not know anything about whether staging or life? And you would open this to all kinds of errors by the end user? Hardly better.

::The aspx and code behind pages total 11,000 lines of code and the db has a couple
::hundred columns in it. And before I get hammered on normalization... "My boss made me
::do it!!!"

What an idiotic approach. Sorry. As you said :-)

11.000 lines of code - I just hope that most is in the HTML part. Tell your boss now it is time top pay for a broken design.|||So whatis the best way to do this? I don't want our clients accessing or updating our production database until the data has been reviewed and approved.

As for the db size and line code, Ihave proposed a normalization plan. This is the project that I inherited a few months back that's been through three sets of external development teams before coming in house. I have had to just add to what they put together because internally it was not viewed as expedient to do a major rewrite. I explained the potential problems and it was taken note of.

I guess the fortunate part of this (if there could possibly be a bright side) is that the form in question will only be used a few times a day by 1 or 2 users, and I am doubtful that most of our clients are disciplined enough to update their information consistently (which might be once every 2-3 months.)

When it was recommended that I use a pending column, I assumed that I would have to duplicate each colum in the table. I felt that taking the time to do that would be worth the extra time investment of normalizing the whole thing, that does not address the current problem I know; but it got me to thinking that if I am going to go back and normalize the db, then I am digging myself into a deeper hole by creating more dependencies.

When I got my hands on this thing and saw it, I was not a happy camper. But I think the mind-set here was, "it works right?" And i'm thinking, "yeah, for the moment."

I appreciate the responses and guidance.

Thanks.|||VERY good question. I would propably go with another table in the same database - unless you want to add versioning to the main table echanism, which may be an overhead.

But another database just looks a little too much over the top.

Definitly a great candidate for a lot of future functionality and normalisation, though.

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?
Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used for
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/database...ilesystem.html
John
"Farhad" wrote:

> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used for
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
John
"Farhad" wrote:
> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

database

We are a couple of student on the area of system development. We are
trying to make a database system that will treat the manipulation and
updating of sounds. The input/output of it, has to be sounds in
graphic, acoustic, form etc. Are there any database systems that
support these kinds of data?Hi
SQL Server 2005 does have BLOB datatypes
http://msdn2.microsoft.com/en-US/library/ms131277.aspx
http://msdn2.microsoft.com/en-us/library/ms130896.aspx and a XML datatype
http://msdn2.microsoft.com/en-us/library/ms131375.aspx that could be used fo
r
holding this type of data; although you may wish to store these files on the
O/S see
http://databases.aspfaq.com/databas...filesystem.html
John
"Farhad" wrote:

> We are a couple of student on the area of system development. We are
> trying to make a database system that will treat the manipulation and
> updating of sounds. The input/output of it, has to be sounds in
> graphic, acoustic, form etc. Are there any database systems that
> support these kinds of data?
>

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log sh
ipping, but these just give a replica of the source data and does not flag the updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.
Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. The difficulty then is in trying to identify what has changed. Another route is to try and identify fields within the production data that will identify when it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.
|||Timestamp your source data when it gets changed. That is definately the best way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multiple data sources which are aggregated in a staging area. This is then denormalised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the source data for import? We have had some bad experiences with triggers on our source database in the past, so would rather not use these. I have considered replication and log
shipping, but these just give a replica of the source data and does not flag the updated/new data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.

Data warehouse data refresh/update

Hello,
Im a member of a team planning a data warehousing project. We have multiple
data sources which are aggregated in a staging area. This is then denormalis
ed and imported into the datawarehouse database.
I am looking at ideas for incremental data refresh, rather than a drop and r
e-import of all data. This would allow us to have historic data.
Does anyone have any tips that might be helpful for detecting changes in the
source data for import? We have had some bad experiences with triggers on o
ur source database in the past, so would rather not use these. I have consid
ered replication and log sh
ipping, but these just give a replica of the source data and does not flag t
he updated/new data.
Any help would be greatly appreciated.
Thanks.
Ben.Look in SQL Books Online for CHECKSUM_AGG to identify changes in a table. Th
e difficulty then is in trying to identify what has changed. Another route i
s to try and identify fields within the production data that will identify w
hen it was last changed. Ty
pically these tend to be datetime, timestamp or rowversion data types.
If you keep production keys in your fact table as additional attributes then
this will give you another option for identifying new data.
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.|||Timestamp your source data when it gets changed. That is definately the best
way.
Rgards
Jamie
"Ben" wrote:

> Hello,
> Im a member of a team planning a data warehousing project. We have multipl
e data sources which are aggregated in a staging area. This is then denormal
ised and imported into the datawarehouse database.
> I am looking at ideas for incremental data refresh, rather than a drop and
re-import of all data. This would allow us to have historic data.
> Does anyone have any tips that might be helpful for detecting changes in the sourc
e data for import? We have had some bad experiences with triggers on our source data
base in the past, so would rather not use these. I have considered replication and l
og
shipping, but these just give a replica of the source data and does not flag the updated/new
data.
> Any help would be greatly appreciated.
> Thanks.
> Ben.