Database for my site

Hello. I am not new to SQL Server but want to setup my own web site with a directory like 123aspx.com or like 411asp.net. I am not sure how to go about the structure of the data so I am going to try several different structures to hold the resources. Does anyone have any experience setting something like this up? I would take any ideas on the subject too. The site that I will hopefully have up and running is located at http://www.thedotnetzone.com.

Thanks in advance

ChadWhat kind of informations you need

______________
Paulo Gonalves

Database for internet applications

I have a colleague who wants to build a web application that is expected to
have a fairly low number of web requests and have this site capture data in
a database. His heart is set upon Access as the back-end database.
Now, I'm happy to accept that Access is a great product, but I know very
little about it - I've always used SQL Server. My gut reaction is to
suggest that he uses SQL Server Express in preference to Access, but
unfortunately I don't know enough about Access to argue why it shouldn't be
used as a database behind a web application. I don't even know much about
the licensing model.
Could someone provide the details here (or persuade me otherwise)?
Thanks everyoneGriff
SQL Server 2005 Express Edition is free but limited to 4GB db size.If I
remeber well Access is limited to 2GB db size.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||On Oct 18, 6:18 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> I have a colleague who wants to build a web application that is expected to
> have a fairly low number of web requests and have this site capture data in
> a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't be
> used as a database behind a web application. I don't even know much about
> the licensing model.
Some notes here:
http://members.optushome.com.au/anandasim/asp.htm
You don't have to worry about licensing. Because when you use
Access .mdb as a back end database, you are not using an Access
license - you are using IIS + ADO/ADO.NET + mdb - Access does not even
need to be installed on the webserver.
One concern is .mdb is not 24x7 - you need to take it offline from
time to time to pack the database and back it up - on the other hand,
if you get the time slot and scheduled batch jobs running, you can
just grab the .mdb and put it in your pocket.
One thing to consider is if you are using a development IDE against
the Access .mdb will there be design time issues with controls and
connections working properly - I used to have those years ago. And Jet
is single threaded - when I had SQL that was half baked during
development, the whole web server would be unresponsive.
HTH
Ananda|||The salomic decission
Use SQL server as the datastore & msAccess as a FrontEnd for maintenance,
reports etc
For the solution you describe the only drawbacks of using Access as a
Back-end is that it has no built in maintenance plan,
must be "offline" for backup, can get corrupted.
Pieter
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||There are no licensing issues with supplying data from an Access database
file to a web page.
There is some advantage in using an Access file to store the data, if you
need to enter, query, and report on the data from within Access.
It is not difficult for someone with experience in something like ASP or PHP
to design a web page where data is read from and/or written to an Access
database.
Access data is not really secure, so you need to ensure you handle the
security at another level. For example, last year I was able to download the
entire MDB from a computer hardware vendor because the database went
corrupt, and the error message showed the name and location of the file. In
this case, the db contained only information that was on the web (i.e. parts
and prices, not customers and orders) so no harm was done (though I did
notify them.)
If you just need to export the data from an Access table to a web page (e.g.
a product listing that needs to be re-exported whenever products or prices
change), you can also do that easily. Here's an example:
http://allenbrowne.com/AppOutputHtml.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone|||One thing about Access as far as web applications are concerned is that it
is still a file. If I happen to know the physical path where the Access
database file is stored (a vulnerability exposed can do this), I can
download the database file from the site ;-)
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I have a colleague who wants to build a web application that is expected to
>have a fairly low number of web requests and have this site capture data in
>a database. His heart is set upon Access as the back-end database.
> Now, I'm happy to accept that Access is a great product, but I know very
> little about it - I've always used SQL Server. My gut reaction is to
> suggest that he uses SQL Server Express in preference to Access, but
> unfortunately I don't know enough about Access to argue why it shouldn't
> be used as a database behind a web application. I don't even know much
> about the licensing model.
> Could someone provide the details here (or persuade me otherwise)?
> Thanks everyone
>|||Wow - thanks for all the feed-back everyone.
Another thing I've just thought of... Access didn't have the concept of
stored procedures - I don't know if the latest version now does. If not,
then it's of course more vulnerable to SQL Injection attacks.|||You can emulate stored procedures in Access but only in version 2002 and
higher altough a few claim that Access 2000 can do it too. There are some
limitations though such as no temp tables, output parameters cannot be used,
does not use the @. character
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:e%231tviXEIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Wow - thanks for all the feed-back everyone.
> Another thing I've just thought of... Access didn't have the concept of
> stored procedures - I don't know if the latest version now does. If not,
> then it's of course more vulnerable to SQL Injection attacks.
>|||In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
@.hotmail.com says...
> I'm happy to accept that Access is a great product
Access is only good for single user database tasks, it's always been
unstable for more than one user at at time.
Use one of the MSDE SQL versions or other versions for your solution and
you'll be a lot happier than using Access.
If you need reports you can use MS Access to pull them, but don't use
Access for a database.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Leythos wrote:
> In article <uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl>, griffithsj_520
> @.hotmail.com says...
>> I'm happy to accept that Access is a great product
> Access is only good for single user database tasks, it's always been
> unstable for more than one user at at time.
> Use one of the MSDE SQL versions or other versions for your solution and
> you'll be a lot happier than using Access.
> If you need reports you can use MS Access to pull them, but don't use
> Access for a database.
You probably missed the fact that this thread is crossposted to an
Access NG, or do you really believe what you wrote about Access?
--
Roy-Vidar|||Allen,
I took a look at your link with some interest, do you know how I can go
about doing the reverse; importing data Into Access 2007 from a webpage;
specifically Googlemaps. Here's what I want to do:
I want to click on an address filed in my Access form and take that address
to googlemaps and have it return a Latitude, longtitude. Before I continue,
let me explain that I already have a fully functioning database with a form
that does the first part succesffuly, namely sending the address (in the
correct google friendly format) to googlemaps, and the browser succesfully
finds the address, and using a combination of a single line Javascript (not
authored by me, I don't understand Java) and some cumbersome Access Sendkeys
I am able to get the Lat/Long and pasted it back into the field. This
approach has it's drawbacks, it involves Alt-tabbing two application windows
and when the net connection is busy, then the timing of the Senkeys gets out
of sequence and the whole process fails, If I increase the timings, then the
process becomes to slow. In summary, it is a bit of a Heath-Robinson setup.
So I was wondering if you could give me some pointers and an understanding
on how to go about automating the process properly. I can understand VBA to a
certain extent, but really don't know where to start. What do you think, can
it be done?
"Allen Browne" wrote:
> There are no licensing issues with supplying data from an Access database
> file to a web page.
> There is some advantage in using an Access file to store the data, if you
> need to enter, query, and report on the data from within Access.
> It is not difficult for someone with experience in something like ASP or PHP
> to design a web page where data is read from and/or written to an Access
> database.
> Access data is not really secure, so you need to ensure you handle the
> security at another level. For example, last year I was able to download the
> entire MDB from a computer hardware vendor because the database went
> corrupt, and the error message showed the name and location of the file. In
> this case, the db contained only information that was on the web (i.e. parts
> and prices, not customers and orders) so no harm was done (though I did
> notify them.)
> If you just need to export the data from an Access table to a web page (e.g.
> a product listing that needs to be re-exported whenever products or prices
> change), you can also do that easily. Here's an example:
> http://allenbrowne.com/AppOutputHtml.html
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Griff" <griffithsj_520@.hotmail.com> wrote in message
> news:uqkH79VEIHA.4748@.TK2MSFTNGP06.phx.gbl...
> >I have a colleague who wants to build a web application that is expected to
> >have a fairly low number of web requests and have this site capture data in
> >a database. His heart is set upon Access as the back-end database.
> >
> > Now, I'm happy to accept that Access is a great product, but I know very
> > little about it - I've always used SQL Server. My gut reaction is to
> > suggest that he uses SQL Server Express in preference to Access, but
> > unfortunately I don't know enough about Access to argue why it shouldn't
> > be used as a database behind a web application. I don't even know much
> > about the licensing model.
> >
> > Could someone provide the details here (or persuade me otherwise)?
> >
> > Thanks everyone
>

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'true')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- Property is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE + '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID => @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database Engine Tuning Advisor Wont Suggest Any Changes :-)

Hi all,
I'm having a problem with the DETA in SQL Server 2005. Or at least I
think I am.
I have an asp.net web application that invokes lots of stored
procedures. There is one page that causes a whole load of stored
procedures to be fired off. I'm pretty sure that some of these
procedures could be optimised somehow.
I've done a trace using SQL Profiler. This basically involved me setting
up the trace and fannying about with the page for a few minutes,
performing the sort of actions that users will commonly perform.
In the DETA I set up a new session, selected the workload file and the
workload database and set it off on its merry way. I set it to consider
getting rid of all indexes (there are only indexes on the PK at the moment).
The thing is, I get no suggestions, which I'm surprised about.
The SProcs that are getting executed are search procs that take a few
different parameters. I had a feeling that this procedure, given the
number of times it's executed and the fact that it has no indexes other
than the PK would be ripe for some optimisation.
Can anyone tell me if:
- Perhaps the workload file is to small? I only fannied for a couple of
minutes. Could that make a difference?
- Is there anything else I could do?
Could I just be a god like query writer and not even know it :-)
Thanks to anyone who can advise
Kindest Regards
SimonHi Simon
First of all I'd say that DETA (2005) is much more reliable rather TA
(2000) and make really good suggestions as i have been played with it
Can you show us your store procedure's code?
I did some testing and have found that DETA does make suggestion to create
an additional index on searched column.
CREATE PROC usp_Test
@.s VARCHAR(20)
AS
SELECT ProductDescription FROM Products WHERE ProductDescription LIKE
@.s+'%'
An output about 1500 rows
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:O8NYdEFJHHA.1008@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm having a problem with the DETA in SQL Server 2005. Or at least I think
> I am.
> I have an asp.net web application that invokes lots of stored procedures.
> There is one page that causes a whole load of stored procedures to be
> fired off. I'm pretty sure that some of these procedures could be
> optimised somehow.
> I've done a trace using SQL Profiler. This basically involved me setting
> up the trace and fannying about with the page for a few minutes,
> performing the sort of actions that users will commonly perform.
> In the DETA I set up a new session, selected the workload file and the
> workload database and set it off on its merry way. I set it to consider
> getting rid of all indexes (there are only indexes on the PK at the
> moment).
> The thing is, I get no suggestions, which I'm surprised about.
> The SProcs that are getting executed are search procs that take a few
> different parameters. I had a feeling that this procedure, given the
> number of times it's executed and the fact that it has no indexes other
> than the PK would be ripe for some optimisation.
> Can anyone tell me if:
> - Perhaps the workload file is to small? I only fannied for a couple of
> minutes. Could that make a difference?
> - Is there anything else I could do?
> Could I just be a god like query writer and not even know it :-)
> Thanks to anyone who can advise
> Kindest Regards
> Simon|||Hi,
Thanks for your reply. The SPROC is as follows:
Apologies for the formating...I'm hoping it will look better on your
screen than it looks as I'm writing this :-)
Any suggestions on indexes would be very much appreciated. In terms of
context, the procedure is attempting to find properties that dont have
rental bookings in them between the dates passed in. The other
paramaters are optional...
Thanks
Simon
CREATE PROCEDURE [dbo].[SearchAvailability]
@.propertyIDOrAddress VARCHAR(75) = NULL,
@.startDate VARCHAR(50) = NULL,
@.endDate VARCHAR(50) = NULL,
@.propertyTypeID smallint = NULL,
@.noOfBedrooms smallint = NULL,
@.estateID smallint = NULL,
@.noOfResults smallint = 25
AS
SELECT
SharedDataStore.dbo.Properties.[ID],
SharedDataStore.dbo.Properties.[StrapLine],
SharedDataStore.dbo.Properties.[ShortDescription],
SharedDataStore.dbo.Properties.[AdditionalDetails],
SharedDataStore.dbo.Properties.[InternalNotes],
SharedDataStore.dbo.Properties.[PublicNotes],
SharedDataStore.dbo.Properties.[Address1],
SharedDataStore.dbo.Properties.[Address2],
SharedDataStore.dbo.Properties.[Address3],
SharedDataStore.dbo.Properties.[EstateID],
SharedDataStore.dbo.Estates.[Name] AS 'EstateName',
SharedDataStore.dbo.Properties.[City],
SharedDataStore.dbo.Properties.[Region],
SharedDataStore.dbo.Properties.[Country],
SharedDataStore.dbo.Properties.[PostalCode],
SharedDataStore.dbo.Properties.[Tel],
SharedDataStore.dbo.Properties.[IsCommitment],
SharedDataStore.dbo.Properties.[PropertyTypeID],
SharedDataStore.dbo.PropertyTypes.[Type] AS 'PropertyTypeName',
SharedDataStore.dbo.Properties.[Status],
SharedDataStore.dbo.PropertyStatusTypes.[Name] AS 'StatusName',
SharedDataStore.dbo.Properties.[NoOfBedrooms],
SharedDataStore.dbo.Properties.[NoOfBathrooms],
SharedDataStore.dbo.Properties.[SupplierID],
SharedDataStore.dbo.Organisations.[Name] AS 'SupplierName',
SharedDataStore.dbo.Properties.[IsDeleted],
SharedDataStore.dbo.Properties.[EnteredDate],
SharedDataStore.dbo.Properties.[EnteredBy],
SharedDataStore.dbo.Properties.[LastModified],
SharedDataStore.dbo.Properties.[LastModifiedBy]
FROM
SharedDataStore.dbo.Properties LEFT JOIN
SharedDataStore.dbo.PropertyTypes ON
SharedDataStore.dbo.Properties.PropertyTypeID =
SharedDataStore.dbo.PropertyTypes.ID
LEFT JOIN SharedDataStore.dbo.Estates ON
SharedDataStore.dbo.Properties.EstateID = SharedDataStore.dbo.Estates.ID
LEFT JOIN SharedDataStore.dbo.Organisations ON
SharedDataStore.dbo.Properties.SupplierID =
SharedDataStore.dbo.Organisations.ID
LEFT JOIN SharedDataStore.dbo.PropertyStatusTypes ON
SharedDataStore.dbo.Properties.Status =
SharedDataStore.dbo.PropertyStatusTypes.ID
WHERE
[SharedDataStore].[dbo].[Properties].[ID] NOT IN
(
SELECT [Bookings].[PropertyID]
FROM Bookings
WHERE ArriveDate < CONVERT(DATETIME, @.endDate, 101) AND DepartDate >
CONVERT(DATETIME, @.startDate, 101)
AND ([Bookings].[IsDeleted] != 'true')
)
AND ([SharedDataStore].[dbo].[Properties].[IsDeleted] != 'tr
ue')
AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE +
'%' +
@.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
@.propertyTypeID) OR (@.propertyTypeID IS NULL))
AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noOfB
edrooms)
OR (@.noOfBedrooms IS NULL))
AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateID)
OR
(@.estateID IS NULL))
AND ([SharedDataStore].[dbo].[Properties].[Status] = 0) -- P
roperty is
active
ORDER BY [SharedDataStore].[dbo].[Properties].[Address1]|||Simon Harvey wrote: [snip]
> AND (([SharedDataStore].[dbo].[Properties].[Address1] LIKE
+ '%' +
> @.propertyIDOrAddress + '%') OR (@.propertyIDOrAddress IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].PropertyTypeID =
> @.propertyTypeID) OR (@.propertyTypeID IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].NoOfBedrooms = @.noO
fBedrooms)
> OR (@.noOfBedrooms IS NULL))
> AND (([SharedDataStore].[dbo].[Properties].EstateID = @.estateI
D) OR
> (@.estateID IS NULL))
Simon,
For these types of queries hardly any optimizations are possible because
of all optional parameters. So I am not surprised that the DETA could
not come up with any suggestions (there probably aren't any useful index
suggestions).
You could consider using dynamic SQL to form the exact SQL query that is
needed for the particular situation. You might get more compilations,
but such a query can make use of indexes on the search column(s) that
is/are used.
Another method to achieve the same thing is to write a query for each
combination of the optional parameters and use IF/ELSE commands to
execute the right query.
Then you can create indexes on each search column and compound indexes
on the search column combinations that are used most.
HTH,
Gert-Jan|||Hi there,
I was begining to suspect that that was going to be the problem.
I did the SProc that way because it seemed a bit (though not much) neater.
I guess if performance becomes more of a problem I may have to look at
making it dynamic.
Many thanks for your advice
Kindest Regards
Simon

Database display/manipulation

I have a web page called homepage.aspx created in Visual Studio 2005 using VB. It contains some hyperlinks on the left side. I have a database created in SQL Server 2005. I need to add, delete, edit, update data in the database directly from this same page. How can I achieve this?. i.e is it possible to, say, have a form on a section of this same web page that would allow me to directly manipulate/modify and update the database? (If you may give code, you may use abstract names for the database, tables and column and/or field names-I will understand). Many thanks in advance.

Yes, what you want to do is possible - and is one of the main points behind ASP.NET. You need to read some of the many free tutorials that are available. It's not the sort of thing anyone can describe in useful detail in a reply to a post in this forum. Here are a couple:

http://msconline.maconstate.edu/tutorials/ASPNET20/default.htm
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx

|||

The links above are useful in this regard. The question I have is a bit different. I have arleady written the code to manipulate the database using GridView control. This ofcourse works in conjunction with the SqlDataSource control. The SqlDataSource control permits selecting a single database table at a time for actions of delete, add, edit etc. This means that if I need to do the above actions on many tables, I will need to reconfigure the SqlDataSource for each table. This also means that I will be able to delete, add, update data in all tables by using a number of web pages as each each table manipulation will have to be done via a seperate web page. This is ok but is very usable. I want to put,say, a form on just one web page and then this form will allow me to perform actions of add, delete, update etc on all tables using just this page. In brief: How can I fit a form on the existing web page and use this form to add, delete, udate the entire database data?.

|||The SqlDataSource will take any valid SQL or the name of a stored procedure for its CommandText property. You can assign this value at runtime in the code-behind. Most applications of the type you are describing tend to not show one "table" at a time - they show data from related tables using joins. Each data maintenance task usually has its own page/form, so adding/editing/deleting a story, for example would all be managed from one page, whereas adding/editing/deleting a contact would be done on a different one. You could do it all in one page, but I would never do so. Maintenance of code would be a mightmare.

database diagram in server explorer

I've created a connection to an SQL 2000 server in an asp.net 2.0 web application using Visual Studio 2005. When I view the database node in the server explorer, it doesn't show the database diagram node. Is there something I need to load to make this node appear? Thanks.

You might want to post this question to the Visual Studio group. I can't recall if this facility is version dependent. They would be able to better answer your question.

Terrence Nevins

SQL Server Program Manager

|||

Unfortunately, Management Studio 2005 and Visual Studio 2005 cannot work with diagrams on SQL Server 2000 servers.

Best regards,
Steve

database diagram in server explorer

I've created a connection to an SQL 2000 server in an asp.net 2.0 web application using Visual Studio 2005. When I view the database node in the server explorer, it doesn't show the database diagram node. Is there something I need to load to make this node appear? Thanks.

You might want to post this question to the Visual Studio group. I can't recall if this facility is version dependent. They would be able to better answer your question.

Terrence Nevins

SQL Server Program Manager

|||

Unfortunately, Management Studio 2005 and Visual Studio 2005 cannot work with diagrams on SQL Server 2000 servers.

Best regards,
Steve

database developed by SQL Server 2005 Express edition can not be deployed in IIS?

I developed a site using Visual Web Developer 2005 and the database used is SQL Server 2005 Express edition.

Everything works fine. However, when I move the site to my IIS server, it says that my database is read-only.

Is it because my SQL Server is an express edition?

No. It's because you haven't configured the server correctly. Post the full error or Google it.

Jeff

|||

Hi Windsnow,

Please check the following:

1. Is the database attached to the SQL Server properly?
2. Please configure the users in the database and map them to the logins on the server.

|||

Kevin Yu

I am having the same issue, somewhat. You asked "Is the database attached to the SQL Server properly?" Could you explain more or point me to a location about attaching to the SQL Server?

Thanks!

James

|||

Hi Jame,

I mean that when we move the database to another server, we have to detach the database file from the service and attach it to another server.

Here are 2 useful articles for your reference.

http://msdn2.microsoft.com/en-us/library/ms190209.aspx
http://msdn2.microsoft.com/en-us/library/ms189625.aspx

Also, please try to check if the permission of the users has been set properly. Because the logins will not go with the database file. And you need to map the logins on the new server to the corresponding users in the database.

|||Thanks... That helped out a lot.

Database design question for experts

Hi,

I have 2 design related questions.

Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(add\update\search), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items).

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

Please give us your expert input\suggestions\tips that will guide our team to the right direction.

Thanks

Jennifer

The size of the database you're describing is nothing out of theordinary for an SQL Server instalation. More important than the numberof records is the number and type of queries you expect to be runningper second. If a single beefy (multi CPU Xeon with as much memory asthe system will take, fast IO subsystem) SQL Server can not handle theload then you could add more servers and build a cluster. I'm no expertat these matter and frankly if you need to come to this forum to learnabout these things the project may be a bit outside of your team'scapabilities. Assuming this is a professional, commercial applicationyou're probably better of hiring an SQL Server expert as a consultantfor a few weeks. But his or her effectiveness will be limited by howwell you know what you're trying to achieve.
In the meantime I recommend you do some experiments. Build a databaseserver and populate it with tables and arbitrary data. Then write someof the more complex and frequently called queries you expect to run.See how many of those you can run in a minute. That will give you someidea what performance limitations you're looking at.
One ASP.NET app that I wrote mines a database to generate a test resultreport. Depending on parameters it can run about 2000 queries, half ofthose are joins over 5 tables or more. The report completes in a minuteor two. This includes generating all the ASP.NET webcontrols etc. Themachine is a dual Xeon 2.8G with 2 GB RAM with mirrored SCSI drives.IIS and SQL reside on the same box.
I would not recommend splitting the application into multiple parts Idon't think there's a performance impact either way but you benefitfrom easier management in a single app. I'd be interested to hear otheropinions on this though.

Good luck!
|||

Nocturnal,

Thanks for your detailed post. Our 6 modules will have around 1 MILLION records in the database! even though we will be deleting old records regularly. Do you still call this an ordinary database for an ecommerce application? Here is more on the database internal: we create views by running complex join on the database involving ~10 tables. Each module has one big view against which we run the search queries for each module.

Having this said, do you still think ONE database with ONE web application suits our needs best?

What I am seeing from your comments is that making one WEB application will be easier to manage and performance impact won't hurt comparing to individual app for each module.

Thanks for your expertise valuable suggestions.

|||Hi,
first of all, I'm hardly an expert so take my suggestions with a grain of salt ok? :-)
I didn't mean to say that 1M records is normal for a e-commerceapplication but it's nothing out of the ordinary for SQL Server. SQLServer doesn't care what application stores its data there, after all.1 M e-ecommerce records is the same as 1 M cellphone records, inconcept.
1M records sounds like a big deal but it depends on the average recordsize as well. Imagine a 200 byte record. Loading the entire table inmemory will take only 200 MB plus overhead. Let's be conservative andcall the overhead 2x. That's 400 MB. Hardly worth mentioning on aserver that has 4 GB of RAM or more. Add some smart indexing and thistable can be searched very quickly.
There are volumes and volumes written about database optimization. Iknow very little about the subject and it's beyond what a forum canreally teach anyway. You should be able to learn the essence from acombination of studying the right books, experimentation and plain ol'experience.
It is good that you have some grasp of the scalability requirements ofyour application. Many designers don't. Now take it one step furtherand start experimenting with your database structures. Create somerepresentative tables and fill them with 1M random records. Then runthe queries against it that you expect your web app to run. Measureperformance. Measure server impact (watch for high CPU utilization andswapping). Modify the experiment, draw conclusions, refine your design.There's really no substitute for experimentation. Have a go at it andlet us know!
|||

jennifer7290 wrote:

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc.

You should probably start by having proper names for your tables. A table represents a SET of entities. "Modules", not "module". Also, the name "forsell" and "shopping" makes very little sense.What is a "shopping"? Use names that describe what the table contains. See ISO-11179.

jennifer7290 wrote:

The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

The single largest problem you'll face with many applications is the inability to share session state -- session state goes away at the application boundry, so you'll have to develop your own site-wide state management - which may not be so bad, anyway, as a lot of applications I see go with shared Context model.

As far as your database, it doesn't have to reflect your application. A database can have quite literally a crap load of tables (2B is the max I believe) and you'll be more than fine with a few hundred. Separate databases mean separate Roles, Users, etc -- and can be quite a pain to set up. I'd recommend sticking with one -- unlike the application, you have a common "security" module shared by each database.

|||

Hello, good question... a single database or multiple databases?

Let's consider

1) A table with 10 Milion of rows is not a problem for SQL server if we use a proper index

2) 10 Tables of 1 M of rows are better then a table of 10M of rows

3) Performace ofter depends on applications that use a database (use pooling)

4) Make minimal queries and use recorset in memory (1 query of 1000 rows is batter than 10 queries of 100 rows)

I suggest 1 DB.

Hello!

|||Thanks to Alex and Claudio for sharing your valuable input. That really matters. Have a nice day!

Database design question

Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!
Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:

> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>
|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pro...ads/books.mspx
2000 -
http://www.microsoft.com/downloads/d...displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/de...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:

Database design question

Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact o
f
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:

> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail
?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book o
n
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysin
g
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possib
le
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact
of
> the trigger on any oltp activity. You can also get third party application
s
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
>|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pr...oads/books.mspx
2000 -
http://www.microsoft.com/downloads/...&displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/d...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
on
> Create Triggers topic?
>
> "John Bell" wrote:
>

Database design question

Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
> > Hi,
> >
> > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > Server 2000. The budgeting data will build up over a course of period and
> > these historical data will be used for decision making in future budget.
> >
> > My questions:
> > 1. what design approach should I use to store the historical data? Data
> > Mining or Datawarehouse? or other options?
> >
> > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > If yes, where they are saved?
> >
> > Thanks for you help!
> >|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
2000 -
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en
Then search for CREATE TRIGGER...
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
>> Hi
>> The choice of datawarehouse or datamining really need deciding by
>> analysing
>> and producing the requirements for the future needs so can't really be
>> answered with the level of information you have given.
>> Although SQL Server 2000 has no automatic method of auditing, it is
>> possible
>> to implement something using triggers and there is a simple example in
>> the
>> CREATE TRIGGER topic in Books Online. I would recommend that you do the
>> mimimum amount of work require in the trigger and do any
>> aggregation/formatting... as a ofline process. This will reduce the
>> impact of
>> the trigger on any oltp activity. You can also get third party
>> applications
>> that implement auditing for you such as Lumigent's auditdb
>> http://www.lumigent.com/products/auditdb.html
>> HTH
>> John
>>
>> "Mindy" wrote:
>> > Hi,
>> >
>> > I am working on a Budgeting web based application using ASP.NET 2 and
>> > SQL
>> > Server 2000. The budgeting data will build up over a course of period
>> > and
>> > these historical data will be used for decision making in future
>> > budget.
>> >
>> > My questions:
>> > 1. what design approach should I use to store the historical data?
>> > Data
>> > Mining or Datawarehouse? or other options?
>> >
>> > 2. Is there any built in tool in sql 2000 to keep track of the audit
>> > trail?
>> > If yes, where they are saved?
>> >
>> > Thanks for you help!
>> >|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver.asp?frame=true
John
"Mindy" wrote:
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
> > Hi
> >
> > The choice of datawarehouse or datamining really need deciding by analysing
> > and producing the requirements for the future needs so can't really be
> > answered with the level of information you have given.
> >
> > Although SQL Server 2000 has no automatic method of auditing, it is possible
> > to implement something using triggers and there is a simple example in the
> > CREATE TRIGGER topic in Books Online. I would recommend that you do the
> > mimimum amount of work require in the trigger and do any
> > aggregation/formatting... as a ofline process. This will reduce the impact of
> > the trigger on any oltp activity. You can also get third party applications
> > that implement auditing for you such as Lumigent's auditdb
> > http://www.lumigent.com/products/auditdb.html
> >
> > HTH
> >
> > John
> >
> >
> > "Mindy" wrote:
> >
> > > Hi,
> > >
> > > I am working on a Budgeting web based application using ASP.NET 2 and SQL
> > > Server 2000. The budgeting data will build up over a course of period and
> > > these historical data will be used for decision making in future budget.
> > >
> > > My questions:
> > > 1. what design approach should I use to store the historical data? Data
> > > Mining or Datawarehouse? or other options?
> > >
> > > 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> > > If yes, where they are saved?
> > >
> > > Thanks for you help!
> > >

Database design issue

hi,
we are designing a web application for defect tracking. In this for each
project the user can choose his own template for writing testcases... we nee
d
to persist this data.As you can see there can be any number of textbox in
that template.
We suggested two models,
1. a table with a text column holding the XML format which contains the
template with data .
2. a table containing variable name and value as column such that a crosstab
query would return the template values for a particular project.
But the .net developers suggest creating the table on the fly while the
template is chosen.. Please note once a template is chosen for the project i
t
remains the same.
which one would be better?If you are not going to query within the template and get a subset of
testcases.you can use the XML method.But we don't have validation techniques
for the XML schema in SQL 2000. So if that's not a problem, then storing as
XML should be the best option.
But if you have cases like, the user will select testcase 5 in the template
and edit it and save it, then you are better off with the second option
because manipulation of XML is tough.
In case of option 1, You can take out the XML and edit it and replace the
old one with the new one. If thats fine, then go for option 1.
Hope this helps.

Database design help pls-Landuse

Trying to help a friend design a database for a new project. Trying to figure out entities i need. This will be use on web project, interface will be link to geographical map showing all State in US.

please advise
Thanks

Here's the business infor he gave me

Landuse is a public service network of county and city municipal databases designed to present geographically indexed FACTS for online Internet distribution during the busy and competitive decision-making process.
FACTS are an acronym for: Fast, Accurate, Comprehensive, Timely, Systematic.
FACTS are abstracted; geo-indexed and made available for Internet distribution by Landuse. This daily updated public information service is an important analytical research tool for responsive government agencies and the general public. It is worthy of your inspection and trust
Landuse is a simple and unique map-driven application used to bookmark nationwide real estate databases with menu-driven Township Locator Maps that will visually identify regions, tracts and/or parcels of property relative to pubic information abstracts of record.
Once you have selected a state indexed with Township Locator Maps, Landuse is a four-step drilldown procedure:
Step One is the Township Locator Map, a copyrighted master plan for identifying each and all of the townships in a particular state with four characters [0000].
Step Two is selecting one or more sections; each surveyed to contain 640 acres (more or less) and is numbered, one through thirty-six, within the selected township [01 to 36].
Step Three will produce a selected section divided into four-quarter sections lettered with the NE [1], NW [2], SW [3], SE [4] containing 160 acres, (more or less).
Local county tax assessors prepare property tax maps annually. Each parcel is identified by an assigned tax map parcel identifier, which is our key to index and attach public recorded documents maintained in county courthouses and news releases published by local county newspaper press organizations.
Step Four is a Landuse dual-purpose menu for access to Docufiles (courthouse) and Profiles (newspapers) referenced to the subject property. Daily public information is indexed to the parcel identifiers for access by member associations, government agencies, and corporate sponsors. Upon receipt of daily public information, the data is made available for Internet service, 36 hours later, on a daily, 24-hour, seven-day basisYou really need to talk to your friend about what they've got in mind... They are WAY off on some of their basic assumptions already. If you designed a database to represent land use the way that text describes it, the mis-match between what you store and the real world will be so bad that there will probably be no real use for what you've created.

The MAPS project done for NOAA should be public information by now. They tried to do almost exactly what this seems to be doing, for the purposes of coordinating weather data and alerting the responsible authorities (to remove the dependancy on Weather Reporting Stations for the dissemination of alerts). That project is still underway as far as I know, with ten years of development without a consensus on how to map satelite observations and WRS (Weather Reporting Stations) to responsible authorities!

Until you get a model that more closely resembles the real world, pretty much any effort you put into the project will be wasted.

-PatP

Database Design

Dear All,
I have just visited a colleague who advised me that the best way to design a
database (speed maximisation for end user be it web or vb) is not to have one
large database but to have your data split into 4 databases where the 4
divisions data is stored separately, rather than one larger database that
stores all data that has to be queried through Stored Procedures. The
databases are likely to be stored on the same hard disk so I don't think
there is a Serial I/O benefit to be gained.
These are not going to be large databases (only about 20,000 records). I
just don't know if my colleague is correct. The logic seems correct but where
is the evidence?
Thanks again.
Alastair MacFarlane
Hi Alastair,
You would get more benifits from ensuring you have the required indexes and
constraints in the database model then partioning the database.
The biggest benifit any databse can get is indexes and correct design. From
there and only after should you think about partionaing.
So while partionaing of Very large databases is good , first do the normal
stuff and then worry about it.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:D9B3A0AD-A0EB-4066-A91A-80B9BD0FA4AB@.microsoft.com...
> Dear All,
> I have just visited a colleague who advised me that the best way to design
> a
> database (speed maximisation for end user be it web or vb) is not to have
> one
> large database but to have your data split into 4 databases where the 4
> divisions data is stored separately, rather than one larger database that
> stores all data that has to be queried through Stored Procedures. The
> databases are likely to be stored on the same hard disk so I don't think
> there is a Serial I/O benefit to be gained.
> These are not going to be large databases (only about 20,000 records). I
> just don't know if my colleague is correct. The logic seems correct but
> where
> is the evidence?
> Thanks again.
> Alastair MacFarlane

Database Design

Dear All,
I have just visited a colleague who advised me that the best way to design a
database (speed maximisation for end user be it web or vb) is not to have on
e
large database but to have your data split into 4 databases where the 4
divisions data is stored separately, rather than one larger database that
stores all data that has to be queried through Stored Procedures. The
databases are likely to be stored on the same hard disk so I don't think
there is a Serial I/O benefit to be gained.
These are not going to be large databases (only about 20,000 records). I
just don't know if my colleague is correct. The logic seems correct but wher
e
is the evidence?
Thanks again.
Alastair MacFarlaneHi Alastair,
You would get more benifits from ensuring you have the required indexes and
constraints in the database model then partioning the database.
The biggest benifit any databse can get is indexes and correct design. From
there and only after should you think about partionaing.
So while partionaing of Very large databases is good , first do the normal
stuff and then worry about it.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:D9B3A0AD-A0EB-4066-A91A-80B9BD0FA4AB@.microsoft.com...
> Dear All,
> I have just visited a colleague who advised me that the best way to design
> a
> database (speed maximisation for end user be it web or vb) is not to have
> one
> large database but to have your data split into 4 databases where the 4
> divisions data is stored separately, rather than one larger database that
> stores all data that has to be queried through Stored Procedures. The
> databases are likely to be stored on the same hard disk so I don't think
> there is a Serial I/O benefit to be gained.
> These are not going to be large databases (only about 20,000 records). I
> just don't know if my colleague is correct. The logic seems correct but
> where
> is the evidence?
> Thanks again.
> Alastair MacFarlane

Database Design

I am designing a WEB BASED Accounting Software with ASP and SQL
Server. For this I need some help for the Database design. My design
is as follows.

I)User table: User_id, UserName....
Users (e.g. John Smith) Each User would contain a following Group of
tables

a)Customers
b)Suppliers
c)Bank Accounts
d)Transactions

Tables under :
User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)

As new user is created all the above tables are created at run time.
These tables are created for each and every user. There can be more
than 4 tables (as mentioned above) for one user. These tables will
increase as more users are added. Only thing in support of this design
is that, the record fetching time for a particular user would be
minimum and the table for a particular user will only load in Memory.

IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?

An alternative design can be as follows

Tables:
Users, Customers, Suppliers, BankAccounts, Transactions ....and so
on.

User: User_Id, UserName, .....
Customers: User_Id, Customer_Id,.....
Suppliers: User_Id, Supplier_Id,....
BankAccounts: User_Id, BankAc_Id,....
Transactions: User_Id, Trans_Id.....
..
..
..
..

All these tables would be created at the design time only and as a new
user is created a record is added to the users table. When the user
adds Customer the record is added to the Customers table... and so
on... The problem with this design is that Customers,Suppliers,
BankAccounts... etc tables would contain records for all the users
and thus the record fetching time for a particular user increases as
many times as there are users in the Database. Another problems with
this design is that more than one user would be connected at run time
will access the same tables, and for even a single user the complete
table will be loaded in memory.

WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
PLEASE HELP WITH CONVINCING REASONS.Clearly #2, less maint and if you ever need over all queries or a customer
gets combined it should be a lot easier. Also use Oracle not the other one
since this is an Oracle newsgroup.
Jim
"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.
>
> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions
>
> Tables under :
> User_FinYear_Customers (e.g JohnSmith_02_03_Customers)
> User_FinYear_Suppliers (e.g JohnSmith_02_03_Suppliers)
> User_FinYear_BankAccounts (e.g JohnSmith_02_03_BankAccounts)
> User_FinYear_Transactions (e.g JohnSmith_02_03_Transactions)
> As new user is created all the above tables are created at run time.
> These tables are created for each and every user. There can be more
> than 4 tables (as mentioned above) for one user. These tables will
> increase as more users are added. Only thing in support of this design
> is that, the record fetching time for a particular user would be
> minimum and the table for a particular user will only load in Memory.
> IS IT FEASIBLE TO CREATE ABOUT 20 TABLES FOR EACH NEW USER ADDED TO
> THE DATABASE? WHICH MEANS IF THERE ARE 1000 USERS THERE WOULD BE 20000
> TABLES IN THE DATABASE. THIS CASE CAN GO WORSE IF THERE ARE MORE THAN
> 1000 USERS. WHAT IS BETTER DATABASE DESIGN, MORE TABLES WITH LESS
> RECORDS OR LESS TABLES WITH MORE NO.OF RECORDS?
>
> An alternative design can be as follows
> Tables:
> Users, Customers, Suppliers, BankAccounts, Transactions ....and so
> on.
> User: User_Id, UserName, .....
> Customers: User_Id, Customer_Id,.....
> Suppliers: User_Id, Supplier_Id,....
> BankAccounts: User_Id, BankAc_Id,....
> Transactions: User_Id, Trans_Id.....
> .
> .
> .
> .
> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.
> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.|||"Rushikesh" <rbaiwar@.sify.com> wrote in message
news:2b29906c.0307142123.1d020801@.posting.google.c om...
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

You may need a lot more than just these tables.

> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database.

Oh no, it doesn't!

> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

It is ridiculous if you write your code to do that.

> WHICH DESIGN SHOULD BE USED AS FAR AS SPEED OF SERVER IS CONCERNED?
> PLEASE HELP WITH CONVINCING REASONS.

Second.
Read a few texts about database design and normalization.

--
Cheers
Nuno Souto
wizofoz2k@.yahoo.com.au.nospam|||Rushikesh (rbaiwar@.sify.com) writes:
> I am designing a WEB BASED Accounting Software with ASP and SQL
> Server. For this I need some help for the Database design. My design
> is as follows.

As whether you should use SQL Server or Oracle, I don't have an opinion.
I come from the SQL Server side, but these questions have the same answer
for any enterprise DBMS.

> I)User table: User_id, UserName....
> Users (e.g. John Smith) Each User would contain a following Group of
> tables
> a)Customers
> b)Suppliers
> c)Bank Accounts
> d)Transactions

This is a completely unacceptable solution, and in completely violation
of the relational model. Just forget about it.

> All these tables would be created at the design time only and as a new
> user is created a record is added to the users table. When the user
> adds Customer the record is added to the Customers table... and so
> on... The problem with this design is that Customers,Suppliers,
> BankAccounts... etc tables would contain records for all the users
> and thus the record fetching time for a particular user increases as
> many times as there are users in the Database. Another problems with
> this design is that more than one user would be connected at run time
> will access the same tables, and for even a single user the complete
> table will be loaded in memory.

Your assumptions here are entirely correct. Or to be less polite: they
are flat wrong in places.

An enterprise DBMS are built for implementing this kind of solution.
With proper indexes, the difference in access time to a certain row
if you have 100 rows or million rows in the table is neglible. Or if
you for that matter have 100 million rows.

Neither does an enterprise DBMS load an entire table into memory, because
there is an access to a single row. I cannot speak for Oracle, but SQL
Server will read the pages you access into memory, and if one user is
very active, all his pages may be in cache, whereas the pages for a user
who is on vacation are only on disk. Pages per users? Ah, didn't I mention
indexes? It does seem reasonable from you mentioned to have clustered
indexes on user ids.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database Design

Dear All,
I have just visited a colleague who advised me that the best way to design a
database (speed maximisation for end user be it web or vb) is not to have one
large database but to have your data split into 4 databases where the 4
divisions data is stored separately, rather than one larger database that
stores all data that has to be queried through Stored Procedures. The
databases are likely to be stored on the same hard disk so I don't think
there is a Serial I/O benefit to be gained.
These are not going to be large databases (only about 20,000 records). I
just don't know if my colleague is correct. The logic seems correct but where
is the evidence?
Thanks again.
Alastair MacFarlaneHi Alastair,
You would get more benifits from ensuring you have the required indexes and
constraints in the database model then partioning the database.
The biggest benifit any databse can get is indexes and correct design. From
there and only after should you think about partionaing.
So while partionaing of Very large databases is good , first do the normal
stuff and then worry about it.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:D9B3A0AD-A0EB-4066-A91A-80B9BD0FA4AB@.microsoft.com...
> Dear All,
> I have just visited a colleague who advised me that the best way to design
> a
> database (speed maximisation for end user be it web or vb) is not to have
> one
> large database but to have your data split into 4 databases where the 4
> divisions data is stored separately, rather than one larger database that
> stores all data that has to be queried through Stored Procedures. The
> databases are likely to be stored on the same hard disk so I don't think
> there is a Serial I/O benefit to be gained.
> These are not going to be large databases (only about 20,000 records). I
> just don't know if my colleague is correct. The logic seems correct but
> where
> is the evidence?
> Thanks again.
> Alastair MacFarlane

Database Creation

Hello...

I want to develop a web site having two features

1. Online Shopping
2. Forums

Im using SQL Server, ASP.NET and C#. Now the problem is that how do I configure the Databases. Whether I create new database for each or I marge the both things into one database. if i create saperate databases for each of the feature then users have to register for two times, first for forums and second for shopping. I dont want to do this...! I want users to register just for once.

____________
Thanks in adv
Nauman Ahmed

Creating of 2 separated DB will be better.

You can use the registration info in one DB table and to use this DB table for authentication. So, registrations will be one time only

regards