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 Events?

Wondering if it's possible to get an event from sql 2000. That is, instead
of polling every few seconds and refreshing my application, can I register
to receive notification of an update/insert etc?
Thanks,
Mikelookup sp_addalert in BOL. If you know the event ID, you can do this or you
can roll your own raiserrror
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e9TeUY%23$DHA.1288@.TK2MSFTNGP10.phx.gbl...
> Wondering if it's possible to get an event from sql 2000. That is, instead
> of polling every few seconds and refreshing my application, can I register
> to receive notification of an update/insert etc?
> Thanks,
> Mike
>|||Hi,
1. Create a user defined message using sp_addmessage (Refer books online)
2. Create an alert using sp_add_alert (Refer books online) using the message
id create in step 1 3.
3. Create a Operator using sp_add_operator (Refer books online)
4. Create a notification using sp_add_notification(Refer books online) - It
can be Email, Netsend or Pager message
5. In the stored procedure just use RAISERROR (msgid, 16, 1) where ever
required. This will internally call the above 4 steps and send the alert.
Note:
Above steps can be created using Enterprise manager as well.
Thanks
Hari
MCDBA
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e9TeUY#$DHA.1288@.TK2MSFTNGP10.phx.gbl...
> Wondering if it's possible to get an event from sql 2000. That is, instead
> of polling every few seconds and refreshing my application, can I register
> to receive notification of an update/insert etc?
> Thanks,
> Mike
>|||Thanks Hari and Ray, time for me to do some reading.
Regards,
Mike
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uPrvsBCAEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi,
> 1. Create a user defined message using sp_addmessage (Refer books online)
> 2. Create an alert using sp_add_alert (Refer books online) using the
message
> id create in step 1 3.
> 3. Create a Operator using sp_add_operator (Refer books online)
> 4. Create a notification using sp_add_notification(Refer books online) -
It
> can be Email, Netsend or Pager message
> 5. In the stored procedure just use RAISERROR (msgid, 16, 1) where ever
> required. This will internally call the above 4 steps and send the alert.
> Note:
> Above steps can be created using Enterprise manager as well.
> Thanks
> Hari
> MCDBA
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:e9TeUY#$DHA.1288@.TK2MSFTNGP10.phx.gbl...
> > Wondering if it's possible to get an event from sql 2000. That is,
instead
> > of polling every few seconds and refreshing my application, can I
register
> > to receive notification of an update/insert etc?
> >
> > Thanks,
> > Mike
> >
> >
>

Database Events?

Wondering if it's possible to get an event from sql 2000. That is, instead
of polling every few seconds and refreshing my application, can I register
to receive notification of an update/insert etc?
Thanks,
Mikelookup sp_addalert in BOL. If you know the event ID, you can do this or you
can roll your own raiserrror
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e9TeUY%23$DHA.1288@.TK2MSFTNGP10.phx.gbl...
> Wondering if it's possible to get an event from sql 2000. That is, instead
> of polling every few seconds and refreshing my application, can I register
> to receive notification of an update/insert etc?
> Thanks,
> Mike
>|||Hi,
1. Create a user defined message using sp_addmessage (Refer books online)
2. Create an alert using sp_add_alert (Refer books online) using the message
id create in step 1 3.
3. Create a Operator using sp_add_operator (Refer books online)
4. Create a notification using sp_add_notification(Refer books online) - It
can be Email, Netsend or Pager message
5. In the stored procedure just use RAISERROR (msgid, 16, 1) where ever
required. This will internally call the above 4 steps and send the alert.
Note:
Above steps can be created using Enterprise manager as well.
Thanks
Hari
MCDBA
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:e9TeUY#$DHA.1288@.TK2MSFTNGP10.phx.gbl...
> Wondering if it's possible to get an event from sql 2000. That is, instead
> of polling every few seconds and refreshing my application, can I register
> to receive notification of an update/insert etc?
> Thanks,
> Mike
>|||Thanks Hari and Ray, time for me to do some reading.
Regards,
Mike
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:uPrvsBCAEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi,
> 1. Create a user defined message using sp_addmessage (Refer books online)
> 2. Create an alert using sp_add_alert (Refer books online) using the
message
> id create in step 1 3.
> 3. Create a Operator using sp_add_operator (Refer books online)
> 4. Create a notification using sp_add_notification(Refer books online) -
It
> can be Email, Netsend or Pager message
> 5. In the stored procedure just use RAISERROR (msgid, 16, 1) where ever
> required. This will internally call the above 4 steps and send the alert.
> Note:
> Above steps can be created using Enterprise manager as well.
> Thanks
> Hari
> MCDBA
> "Mike Hildner" <mhildner@.afweb.com> wrote in message
> news:e9TeUY#$DHA.1288@.TK2MSFTNGP10.phx.gbl...
instead
register
>

Database error code: 515

We have MS SQL 2000 server. Recently, on our database application we gate
"Databse error code: 515" It says: Cannot insert the value NULL into cilumn
"contact_id", table name, column doesn't allow nulls. INSERT fails. No
changes made to databse. ...
A recent action on this table was, we exported the contact table to Excel,
updated, and imported from Excel.
Would you please advise us what the problem could be and how we can address
this.
Thanks,
Yigedeb.
CBI Inc
Hello,
Just see if you insert NULL into Contact_ID field. Please take a look into
table schema using SP_HELP <TableName> and verify the data you
are trying to insert..
Thanks
Hari
"Yigedeb" <Yigedeb@.discussions.microsoft.com> wrote in message
news:2FBF9D1C-F344-48AA-8172-0FF3E00A00BA@.microsoft.com...
> We have MS SQL 2000 server. Recently, on our database application we gate
> "Databse error code: 515" It says: Cannot insert the value NULL into
> cilumn
> "contact_id", table name, column doesn't allow nulls. INSERT fails. No
> changes made to databse. ...
> A recent action on this table was, we exported the contact table to Excel,
> updated, and imported from Excel.
> Would you please advise us what the problem could be and how we can
> address
> this.
> Thanks,
> Yigedeb.
> CBI

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 DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
Catelin
I have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:

>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
CatelinI have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:

>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
CatelinI have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:
>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database Diagrams / Alter Table

(Repost. Originally posted as a reply in the correct thread.)
You are correct in the assumption that the application works due to the
error checking.
I'm working with Mike on this problem and somewhere along the line, all of
the keys got dropped in the production database. We want to recreate them
in the development database and then run the scripts against the production
database to create the keys and the relationships that are required. Is
there any way to automatically compare two versions of the database and
generate a batch of the ALTER TABLE commands? Or am I looking at
writing each statement individually?
Thanks
Dave
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
> Check if the FKs are in place. Use sp_help and sp_foreignkeys system SPs
to
> check if FKs are present. You can add them, if they are not in place,
using
> the ALTER TABLE command. If the application respects them, if it has input
> validation & error handling, you should have no problem.
>
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Mike" <Mike149@.yahoo.com> wrote in message
> news:312f01c35514$024662e0$a001280a@.phx.gbl...
> > I have a question concerning the diagramming tool in SQL
> > Server 2000. On the development server the keys show up
> > in the table view of the Enterprise Manager and the
> > diagramming tool works as expected. However, when we
> > imported the database into production, the keys did not
> > come forward so the diagramming tool cannot diagram
> > relationships. The interesting thing is that the
> > application works as expected, except the keys dont show
> > up in the table view.
> >
> > Question...how is this possible? How do I add the keys
> > to the design without disrupting the application?
> >
> > Thanx
>
>I am using the diff tool from http://www.adeptsql.com/
they have an 30 day trial of the software.
It does an great job on comparing Databases where the only difference is FK
not in one of them.
I delete on the FKs on one of my test DBs several times yesterday;
and then I use the diff tool to compare my tests DBs to each other and
high-light the FKs and have the Diff tool create the ALTER Table commands.
Note: I cut and paste the command into QA to run them sometimes.
FYI: It does not yet handle permissions on objects.
"Dave" <dave@.glimmernet.com> wrote in message
news:%23PLD0FDWDHA.2464@.TK2MSFTNGP09.phx.gbl...
> (Repost. Originally posted as a reply in the correct thread.)
> You are correct in the assumption that the application works due to the
> error checking.
> I'm working with Mike on this problem and somewhere along the line, all of
> the keys got dropped in the production database. We want to recreate them
> in the development database and then run the scripts against the
production
> database to create the keys and the relationships that are required. Is
> there any way to automatically compare two versions of the database and
> generate a batch of the ALTER TABLE commands? Or am I looking at
> writing each statement individually?
> Thanks
> Dave
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
> > Check if the FKs are in place. Use sp_help and sp_foreignkeys system SPs
> to
> > check if FKs are present. You can add them, if they are not in place,
> using
> > the ALTER TABLE command. If the application respects them, if it has
input
> > validation & error handling, you should have no problem.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Mike" <Mike149@.yahoo.com> wrote in message
> > news:312f01c35514$024662e0$a001280a@.phx.gbl...
> > > I have a question concerning the diagramming tool in SQL
> > > Server 2000. On the development server the keys show up
> > > in the table view of the Enterprise Manager and the
> > > diagramming tool works as expected. However, when we
> > > imported the database into production, the keys did not
> > > come forward so the diagramming tool cannot diagram
> > > relationships. The interesting thing is that the
> > > application works as expected, except the keys dont show
> > > up in the table view.
> > >
> > > Question...how is this possible? How do I add the keys
> > > to the design without disrupting the application?
> > >
> > > Thanx
> >
> >
>
>|||You could also use DB Ghost www.dbghost.com which does
handle object permissions.
>--Original Message--
>This sounds like it'll work for what I need - I'll look
into it.
>Thanks, Tim
>Dave
>
>"Tim S" <stahta01@.juno.com> wrote in message
>news:38xWa.1585$GN6.331@.fe01.atl2.webusenet.com...
>> I am using the diff tool from http://www.adeptsql.com/
>> they have an 30 day trial of the software.
>> It does an great job on comparing Databases where the
only difference is
>FK
>> not in one of them.
>> I delete on the FKs on one of my test DBs several
times yesterday;
>> and then I use the diff tool to compare my tests DBs
to each other and
>> high-light the FKs and have the Diff tool create the
ALTER Table commands.
>> Note: I cut and paste the command into QA to run them
sometimes.
>> FYI: It does not yet handle permissions on objects.
>>
>> "Dave" <dave@.glimmernet.com> wrote in message
>> news:%23PLD0FDWDHA.2464@.TK2MSFTNGP09.phx.gbl...
>> > (Repost. Originally posted as a reply in the
correct thread.)
>> >
>> > You are correct in the assumption that the
application works due to the
>> > error checking.
>> >
>> > I'm working with Mike on this problem and somewhere
along the line, all
>of
>> > the keys got dropped in the production database. We
want to recreate
>them
>> > in the development database and then run the scripts
against the
>> production
>> > database to create the keys and the relationships
that are required. Is
>> > there any way to automatically compare two versions
of the database and
>> > generate a batch of the ALTER TABLE commands? Or am
I looking at
>> > writing each statement individually?
>> >
>> > Thanks
>> >
>> > Dave
>> >
>> >
>> >
>> > "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
>in
>> > message news:uGKT3TRVDHA.2192@.TK2MSFTNGP10.phx.gbl...
>> > > Check if the FKs are in place. Use sp_help and
sp_foreignkeys system
>SPs
>> > to
>> > > check if FKs are present. You can add them, if
they are not in place,
>> > using
>> > > the ALTER TABLE command. If the application
respects them, if it has
>> input
>> > > validation & error handling, you should have no
problem.
>> > >
>> > > --
>> > > Dejan Sarka, SQL Server MVP
>> > > FAQ from Neil & others at:
http://www.sqlserverfaq.com
>> > > Please reply only to the newsgroups.
>> > > PASS - the definitive, global community
>> > > for SQL Server professionals -
http://www.sqlpass.org
>> > >
>> > > "Mike" <Mike149@.yahoo.com> wrote in message
>> > > news:312f01c35514$024662e0$a001280a@.phx.gbl...
>> > > > I have a question concerning the diagramming
tool in SQL
>> > > > Server 2000. On the development server the keys
show up
>> > > > in the table view of the Enterprise Manager and
the
>> > > > diagramming tool works as expected. However,
when we
>> > > > imported the database into production, the keys
did not
>> > > > come forward so the diagramming tool cannot
diagram
>> > > > relationships. The interesting thing is that the
>> > > > application works as expected, except the keys
dont show
>> > > > up in the table view.
>> > > >
>> > > > Question...how is this possible? How do I add
the keys
>> > > > to the design without disrupting the application?
>> > > >
>> > > > Thanx
>> > >
>> > >
>> >
>> >
>> >
>>
>
>.
>

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 Design setup

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
--
David Portas
SQL Server MVP
--|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
--
David Portas
SQL Server MVP
--|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?

Database Design setup

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?
Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
David Portas
SQL Server MVP
|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>
|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...

> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.
|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?
|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
David Portas
SQL Server MVP
|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?

Database design question....

I am building an application which is part directory (yellow page style). As I began to build the database and write up the design I found my self challenged with the following:

In part of the db I have a table for companies, categories and subcategories. The problem is that some of the companies fall into more than 1 category and I am not sure how to effeciently design this.

Can anyone share some thoughts or suggestions?::The problem is that some of the companies fall into more than 1 category and I am not
::sure how to effeciently design this.

What you need to make is a m:n relation and is a standard pattern for handling this standard problem in db design.

First, you should have three tables, but NOT two for category and subcategory.

You should have one table for Category (and a subcategory IS a category, build a tree using a relation pointing back to the table), one for the entires (companies), one for coupling one entry with one caategory - a mid table that allows you to assign an entry to x categories.

This table would basically contain one field (fk-relation) to the category, one to the entry.

Get a beginner book for SQL and relational databases that explains M:N relations in depth.|||doesn't look that hard, basic relational db design:

Category
* CatID (PK)
* CatName
* ...

Company
* CompID (PK)
* CompName
* ...

Company_Category
* CompID (PK ; FK to Company.CompID)
* CatID (PK ; FK Category.CatID)

By the way, implement subcategories as a category with a parent. Category table becomes:

Category
* CatID
* CatName
* CatParentCatID (maybe NULL for top level category)|||look up tables. Just store the primary keys in the look up tables.

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

I know this isn't SQL Server specific but I've been pondering over
designing a payment system for our existing application which uses SQL
Server 2000.

We have an application that tracks employees and the companies that
they consult for. The employees are in an employee table using an
identity column for the primary key called EmployeeID. The companies
are in a company table, also using an identity column for its PK
called CompanyID.

The employees are contracted out to different companies. Each company
has different responsibilities for paying some of the wages and
benefits of our consultants based on the contract with the employer.

Here are the 4 possible scenarios we need to support:
1) Employee makes payment to a Company's account (e.g. reimbursing
company for personal phone calls done on company equipment, broken
equipment, etc)
2) Employee makes payment to its own account (e.g. personal
contribution to retirement fund, health insurance not covered by
company contract, etc)
3) Company makes payment to an Employee's account (e.g. health
insurance, pension, dental, overtime wages, reimbursements for
personal expenses, etc)
4) Company makes payment to its own account (e.g. consulting fees
charged by our consulting firm, other services our firm provides the
company)

A payment can be broken down into the following elements:
1) Identity column for PK
2) The date/time of the payment
3) Who made the payment (Employee or Company)
4) To which account does the payment go towards (Employee or Company)
5) User who entered the payment into the system (for audit)

A second table is needed to show the details of the payment since the
amounts can go to multiple payment categories (e.g. $50 for Health
Insurance, $75 for retirement fund, $10 for Dental, etc):
1) Identity column for PK
2) PaymentID (FK to link to payment table above)
3) PaymentCategoryID (FK to link to the payment category)
4) Amount

The question lies in how to handle the relationship of the employee
table to the payment table and of the company table to the payment
table. What options do I have when designing this?

The ideal solution would provide referential integrity via the DBMS,
to prevent deleting an employee who made a payment or received money
and to prevent deleting a company who made a payment or received money
in its account.

ThanksOn 10 Sep 2004 08:19:01 -0700, cyansoft wrote:

>I know this isn't SQL Server specific but I've been pondering over
>designing a payment system for our existing application which uses SQL
>Server 2000.
>We have an application that tracks employees and the companies that
>they consult for. The employees are in an employee table using an
>identity column for the primary key called EmployeeID. The companies
>are in a company table, also using an identity column for its PK
>called CompanyID.
>The employees are contracted out to different companies. Each company
>has different responsibilities for paying some of the wages and
>benefits of our consultants based on the contract with the employer.
>Here are the 4 possible scenarios we need to support:
>1) Employee makes payment to a Company's account (e.g. reimbursing
>company for personal phone calls done on company equipment, broken
>equipment, etc)
>2) Employee makes payment to its own account (e.g. personal
>contribution to retirement fund, health insurance not covered by
>company contract, etc)
>3) Company makes payment to an Employee's account (e.g. health
>insurance, pension, dental, overtime wages, reimbursements for
>personal expenses, etc)
>4) Company makes payment to its own account (e.g. consulting fees
>charged by our consulting firm, other services our firm provides the
>company)
>A payment can be broken down into the following elements:
>1) Identity column for PK
>2) The date/time of the payment
>3) Who made the payment (Employee or Company)
>4) To which account does the payment go towards (Employee or Company)
>5) User who entered the payment into the system (for audit)
>A second table is needed to show the details of the payment since the
>amounts can go to multiple payment categories (e.g. $50 for Health
>Insurance, $75 for retirement fund, $10 for Dental, etc):
>1) Identity column for PK
>2) PaymentID (FK to link to payment table above)
>3) PaymentCategoryID (FK to link to the payment category)
>4) Amount
>The question lies in how to handle the relationship of the employee
>table to the payment table and of the company table to the payment
>table. What options do I have when designing this?
>The ideal solution would provide referential integrity via the DBMS,
>to prevent deleting an employee who made a payment or received money
>and to prevent deleting a company who made a payment or received money
>in its account.
>Thanks

Hi Cyansoft,

My tables would probably look something like this:

CREATE TABLE Payments
(PaymentID int NOT NULL IDENTITY,
PayDateTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PaidByEmp int,
PaidByComp int,
PaidToEmp int,
PaidToComp int,
EnteredBy nvarchar(256) NOT NULL,
PRIMARY KEY (PaymentID),
FOREIGN KEY (PaidByEmp) REFERENCES Employees (EmpID),
FOREIGN KEY (PaidByComp) REFERENCES Companies (CompID),
FOREIGN KEY (PaidToEmp) REFERENCES Employees (EmpID),
FOREIGN KEY (PaidToComp) REFERENCES Companies (CompID),
CHECK ((PaidByEmp IS NULL AND PaidByComp IS NOT NULL)
OR (PaidByEmp IS NOT NULL AND PaidByComp IS NULL)),
CHECK ((PaidToEmp IS NULL AND PaidToComp IS NOT NULL)
OR (PaidToEmp IS NOT NULL AND PaidToComp IS NULL))
)

CREATE TABLE PaymentDetails
(PaymentID int NOT NULL,
PaymentCategoryID int NOT NULL,
Amount decimal(8,2) NOT NULL,
PRIMARY KEY (PaymentID, PaymentCategoryID),
FOREIGN KEY (PaymentID) REFERENCES Payments (PaymentID),
FOREIGN KEY (PaymentCategoryID) REFERENCS Categories (CatID)
)

Note that I left out the extra identity column you planned for the details
table; the natural key (payment + category) is just fine in this case.

I'd also try to find out what the natural key for a payment is. I think
the bookkeeping department should be able to fill me in on that. Than I
would add those columns to the table and define a UNIQUE constraint
forthem; I'd also conder using it instead of the identity column. That
would depend on the number of columns and kind of data in the natural key
(as it would propagate to the details table, I'd stick with identity if
the natural key is too cumbersome).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||cyansoft (cyansoft@.hotmail.com) writes:
> A payment can be broken down into the following elements:
> 1) Identity column for PK
> 2) The date/time of the payment
> 3) Who made the payment (Employee or Company)
> 4) To which account does the payment go towards (Employee or Company)
> 5) User who entered the payment into the system (for audit)
> The question lies in how to handle the relationship of the employee
> table to the payment table and of the company table to the payment
> table. What options do I have when designing this?

You could have a Payers table, which would be a supertable of Employees
and Companies. My personal choice, though, would be to have one EmployeeID
and one CompanyID column, and this constraint:

CHECK (EmployeeID IS NULL AND CompanyID IS NOT NULL OR
EmployeeID IS NOT NULL AND CompanyID IS NULL)

That is, requiring that exactly one of the columns should be non-NULL.
Referential integrity is now trivial.

> A second table is needed to show the details of the payment since the
> amounts can go to multiple payment categories (e.g. $50 for Health
> Insurance, $75 for retirement fund, $10 for Dental, etc):
> 1) Identity column for PK
> 2) PaymentID (FK to link to payment table above)
> 3) PaymentCategoryID (FK to link to the payment category)
> 4) Amount

There is no need for an IDETNITY column here as I can see. (PaymentID,
PaymentCategoryID) appears to be a natural primary key to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9560F3C3A2761Yazorman@.127.0.0.1>...
> You could have a Payers table, which would be a supertable of Employees
> and Companies. My personal choice, though, would be to have one EmployeeID
> and one CompanyID column, and this constraint:

Pardon my ignorance(since Googling came up with a bunch of stuff about
Informix), but what exactly is a supertable and why do you prefer
using the check constraint over a supertable in my situation? In what
situation(s) would a supertable be preferred? How would one implement
a supertable in SQL Server 2000?

Thanks|||cyansoft (cyansoft@.hotmail.com) writes:
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9560F3C3A2761Yazorman@.127.0.0.1>...
>> You could have a Payers table, which would be a supertable of Employees
>> and Companies. My personal choice, though, would be to have one
>> EmployeeID and one CompanyID column, and this constraint:
> Pardon my ignorance(since Googling came up with a bunch of stuff about
> Informix), but what exactly is a supertable

A supertable in this context could be called PaymentActors, and the
Employees and Companies tables would both have their PKs as foreign
keys to this table.

> and why do you prefer using the check constraint over a supertable in my
> situation?

Because I feel like it! :-)

Seriously, it is not a simple question, because it is a trade-off. Let's
say that you would have umpteen tables where the entity could be a
company or an employee. Then I would do it. But if it is a one-off, I
prefer to have two columns.

> In what situation(s) would a supertable be preferred? How would one
> implement a supertable in SQL Server 2000?

Check this link for an example from Joe Celko:
http://groups.google.com/groups?ie=...om%3E&lr=&hl=sv

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Database design question

I’m relatively new at database design so forgive me if I’ve overlooked
something stupid.
I’m designing a teacher grading application on the new SQL 2005 Express.
Right now I’ve got a Student table that lists info on each student. I the
n
have a Grades table that lists grades for each assignment that is linked to
an assignment table. The grades table and the student table are linked with
a relationship. I’d like to create a view that lists the student name wit
h
each corresponding grade for each assignment in a new column. When do a
simple select statement I get a row for each grade, I just want one row for
each student with a assignment grade in separate columns. Is this possible?It can be done on the server (in T-SQL, even) but it *really should* be done
in the presentation layer.
Read more here:
http://www.windowsitpro.com/Article...5608/15608.html
Data seems more user-friendly using this technique, however, an important
informational value is lost this way - the relationship. So, I'd suggest
using this technique for presentation purposes only, better yet - let the
presentation engine take care of it.
ML