NEED HELP and GUIDANCE!

I have a HUGE project (at least for me) and need some guidance.

I am trying to create a database for a local university movie club
that allows users to input there basic personal information (name,
address, telephone number) as well as movies in there collection. The
movies will be categorized by genre (comedy, romance, horror, etc.)
and title. I want to be able to let the users add and remove movies
to their list of movies they own, I'll call it "MOVIES I OWN".

The user will also need to be able to create a SECOND list of movies
they would like to see, again they can choose by genre and title.
They need to also be able to add and remove from this list also, i'll
call it "MOVIES I WANT TO SEE".

The last part of the project will be to match the users of "movies i
want to see" with "movies I own" users. It will be displayed on the
local university website when the user logs in and will alert the user
to the match. If there is a match at a later time, maybe the user can
be emailed? Also if there is a match, perhaps the two movies can be
taken off record after the user acknowledges the match. I would need
it to be able to handle a small amount of users now logged in at the
same time , but would like for it to eventually handle several hundred
users logged on at the same time in the distant future with out
performance problems.

I am not sure if sql 2000 is the best to get this done or perhaps
oracle. I am currently trying this on mySQL with PHP but currently am
lost in a forest of data. Any guidance suggestions will be greatly
appreciated. I am pretty new to this so please be kind...Somthing like this maybe:

CREATE TABLE Members (login VARCHAR(10) NOT NULL PRIMARY KEY, membername
VARCHAR(30) NOT NULL UNIQUE);

CREATE TABLE Movies (cat_no CHAR(10) NOT NULL PRIMARY KEY, title VARCHAR(30)
NOT NULL, released INTEGER NOT NULL, UNIQUE (title,released));

CREATE TABLE MembersMovies (login VARCHAR(10) NOT NULL REFERENCES Members
(login), cat_no CHAR(10) REFERENCES Movies (cat_no), media CHAR(3) NOT NULL
CHECK (media IN ('DVD','VHS')), Memberstatus CHAR(1) NOT NULL CHECK
(Memberstatus IN ('H','W') /* "Has" or "Wants" */), PRIMARY KEY
(login,cat_no,Memberstatus));

Query: Who has the movies that member 'X' wants?:

SELECT U.membername, M.title, M.released
FROM MembersMovies AS H,
MembersMovies AS W,
Movies AS M,
Members AS U
WHERE H.cat_no = W.cat_no
AND H.media = W.media
AND H.Memberstatus = 'H'
AND W.Memberstatus = 'W'
AND W.login = 'X'
AND W.cat_no = M.cat_no
AND H.login = U.login ;

> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am

Any of those products should be capable of your specified requirements. I
expect your choice will be guided mostly by what you know and the existing
environment you have to work with.

--
David Portas
SQL Server MVP
--|||dahct@.yahoo.com (new_GUY) wrote in message news:<c978d8cf.0406131603.34edddc9@.posting.google.com>...
> I have a HUGE project (at least for me) and need some guidance.
> I am trying to create a database for a local university movie club
> that allows users to input there basic personal information (name,
> address, telephone number) as well as movies in there collection. The
> movies will be categorized by genre (comedy, romance, horror, etc.)
> and title. I want to be able to let the users add and remove movies
> to their list of movies they own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies
> they would like to see, again they can choose by genre and title.
> They need to also be able to add and remove from this list also, i'll
> call it "MOVIES I WANT TO SEE".
> The last part of the project will be to match the users of "movies i
> want to see" with "movies I own" users. It will be displayed on the
> local university website when the user logs in and will alert the user
> to the match. If there is a match at a later time, maybe the user can
> be emailed? Also if there is a match, perhaps the two movies can be
> taken off record after the user acknowledges the match. I would need
> it to be able to handle a small amount of users now logged in at the
> same time , but would like for it to eventually handle several hundred
> users logged on at the same time in the distant future with out
> performance problems.
> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am
> lost in a forest of data. Any guidance suggestions will be greatly
> appreciated. I am pretty new to this so please be kind...

From the information you've given, it sounds like the database
platform is more or less irrelevant - if you're "lost in a forest of
data", then you probably need to spend some time looking at your
requirements and your data model. You might want to start by searching
Google for existing data models for CD or movie collections, then look
at extending them to allow for multiple users.

I wouldn't worry too much about the database - your application sounds
like many other PHP/MySQL ones (web-based, low budget, few users, no
critical data etc.). Unless you have some clear and compelling reason
for moving to a different database, that's likely to be a distraction
from addressing your key issues.

Simon|||In article <c978d8cf.0406131603.34edddc9@.posting.google.com>,
dahct@.yahoo.com says...
> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am
> lost in a forest of data. Any guidance suggestions will be greatly
> appreciated. I am pretty new to this so please be kind...

If your project is going be connected to a web server, then you are
going to need to stick with something like MySQL - Both Oracle and MS
SQL require licenses, MS SQL requires a CPU license for anything used by
a web server to present data. A CPU license is about $4900 for MS SQL, I
have no idea what Oracle licenses run today.

If this is a class project, that won't hit the web for anyone but
yourself, then you can use the 120 Day eval version without limitation.

Now, to answer your question, based on the small size of your database,
I would say that ANY of the databases you've mentioned should be more
than enough to handle it.

Things to consider when building a database:

1) Database and web server belong on different machines

2) The OS is installed on one set of drives, the database log files on
another set that is mirrored, and the data files are installed on yet
another set of drives configured as RAID 5 or RAID 0+1. For personal use
a single drive with multiple volumes will also do, but the multi-drive
methods is the best option.

3) Size your database ahead of time - meaning if you think it's going to
need 1GB of space, go ahead and make it 1GB, saves grow time later.

4) Make sure that you build proper indexes and clustered indexes

5) Make sure that you don't over-normalize the database, but make sure
it's easy to expand your tables.

6) Memory - make sure you have enough, how much is enough, well, as much
as the OS can handle :) In most cases, based on what you seem to be
building, if your testing platform has 1GB of RAM you should be in good
shape. If you are testing and running the web server and database server
on the same machine, make sure (for MS SQL) that you limit the SQL
Server to 65% of the memory - this saves room for the OS and IIS/PHP so
that the SQL Server doesn't have to spend time releasing memory to the
system.

To handle several hundred users, at the same time, you should consider a
small Dual CPU server with 3GB of RAM for the database and another small
Dual CPU server with 2GB of RAM for the web server. If you don't have
funding, the ASUS PC-DL Deluxe motherboard allows Dual Xeon CPU's, up to
4GB of RAM, and lets you use up to 6 IDE (4 SATA) devices. You could go
cheap and buy 4 120GB SATA drives and use the PC-DL Onboard RAID
controller to build one (4 drive) RAID 5 array, and then partition it
for OS, LOGS, DATA.

One other thing, if you are using a MS platform for the web server,
don't install your web site on the "C" drive, make sure that you have a
"D" drive and install your site there - to many people fail to secure
their systems and it's a lot easier to secure if you have the web site
on the NON-OS drives.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)

Need guidence on notifing when a new users is added

I have a SQL 2000 satabase that has multiple administrators for it and I nee
d
to know when a new user is added to the server. Can you provide some
suggestions on how to accpmplosh this task?"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:9416DA88-2F96-467A-A5DD-ED051E96A99D@.microsoft.com...
>I have a SQL 2000 satabase that has multiple administrators for it and I
>need
> to know when a new user is added to the server. Can you provide some
> suggestions on how to accpmplosh this task?
Do you mean a new LOGIN or a new USER (or both)?
SELECT name, createdate
FROM sysusers
WHERE createdate >= '2006-01-23T00:00:00.000';
SELECT name, createdate
FROM master.dbo.syslogins
WHERE createdate >= '2006-01-23T00:00:00.000';
You could create a job to run these queries once a day or once an hour and
email you the result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

need direction on SSB replacing existing technology

Hello, I have an existing suite of applications in my company where remote laptop users enter data into a local database instance. There are several different applications with several different databases (some users run application A and C, others run A and B and D, etc). All of this data needs to make its way into both their local databases, and to the front office database. In addition, some of the data goes out of the front office to other end users (supervisors get data from people under them).

Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.

I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?

any pointers to articles that go beyond "here's what SSB is" would be appreciated.

matt tagliaferri

Why aren't you considering SQL Server Replication?|||I've used replication for other tasks - I've found it to be a bit difficult to manage and troubleshoot. Ansynchonous messaging seems to most closely match our existing method of sending the data around (scripts via ftp).

matt tag
|||

Implementing replication on top of Service Broker will require a lot of effort in terms of building the plumbing to read/write data, marshalling/unmarshalling, managing schemas, monitoring, configuration, etc. I'm not sure if it actually helps reduce the problem.

Service Broker is useful when you truely want to build loosely-coupled apps. That is apps that are not bound w.r.t. availability, time, schemas, administrative control, identities, scalability, etc. But it is more general purpose than replication and therefore requires more effort.

NEED DIRECTION and suggestions!

I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thou
sand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...
Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>

NEED DIRECTION and suggestions!

I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allo
ws users to input there basic personal information (name, address, telephone
number) as well as movies in there collection. The movies will be categori
zed by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and rem
ove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they wo
uld like to see, again they can choose by genre and title. They need to als
o be able to add and remove from this list also, i'll call it "MOVIES I WANT
TO SEE".
The last part of the project will be to match the users of "movies i want to
see" with "movies I own". It will be displayed on the local university web
site when the user logs in and will alert the user to the match. If there i
s a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two mo
vies can be taken off record. I would need it to be able to handle a small a
mount of users now logged in at the same time , but would like for it to han
dle several hundred to thou
sand users logged on at the same time in the distant future with out perform
ance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am curren
tly trying this on mysql but currently lost in a forest of data. Any guidanc
e suggestions will be greatly appreciated. I am pretty new to this so pleas
e be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mysql but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>

NEED DIRECTION and suggestions!

I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, romance, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time, maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thousand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>sql

need a way to secure RDL from being edited ?

Hi,
Is there absolutely no way at all that I can employ security on an RDL file
to stop users editing them ?
i.e design a report for a site, ship it, upload it to Rep Services (user on
site has admin right to machine - runs IIS and SQL internally). Is there no
way to stop someone opening the RDL in any RDL aware application and
amending it ?
Thanks for your time.
ScottScott,
You can easily secure the built in Report Manager web interface. Under
security properties for the Home directory, add your Windows login as a
Content Manager, then remove BUILTIN\Administrators. You can assign Browser
rights (and editing rights if you so desire) to the appropriate groups at
either the top level or lower down in the tree.
(I assume these users aren't accessing the RS web service directly from a
.NET application. That can be secured, but is a whole other ball of wax.)
Ted
"scott" wrote:
> Hi,
> Is there absolutely no way at all that I can employ security on an RDL file
> to stop users editing them ?
> i.e design a report for a site, ship it, upload it to Rep Services (user on
> site has admin right to machine - runs IIS and SQL internally). Is there no
> way to stop someone opening the RDL in any RDL aware application and
> amending it ?
> Thanks for your time.
> Scott
>
>|||Hi Ted,
Thanks for reply. I understand what you mean about securing the report and
dirs within the web interface but im more concerned about the actual RDL.
For example:
- I write a report and email it to a client
- they upload it into reporting services for users
- reporting services is running internally on IIS that also runs SQL server
- at least one admin user onsite will have admin rights to
sqlserver/iisserver/reportingservices machine.
If they have an RDL aware app they can amend the RDL. I would like to
secure the RDL from amending because:
1. it takes time and effort to write the RDL and we have a very complicated
db relationship structure.
2. we have to support the RDL files and if they have been amended it could
make it impossible.
Thanks for your time.

need a small help on SQL Query.

guys i need a small help on SQL Query.

I want to write a SELECT query which gives me CASE SENSITIVE search result. for ex: if a users original password in db is 'TOM' and user while logging in enters his password as 'tom', he should not be able to login ..But if he enters the password as 'TOM' he is permitted to log in .. How do i check this using a SQL Select Query ?
Any idea on this would be greatly appreciated..

NimeshUsing the PUBS database as an example, I have SQL Server installed as case insensitive. I can write a query to return the Author 'Green' from the AUTHORS table as:

select *
from authors
where au_lname = 'green'

Which would return 1 record even if the lastname is 'Green' with a capital letter. However if I want to enforce case sensitive I would convert the values to VARBINARY. The following would not return a record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'green')

However this would return 1 record:

select *
from authors
where convert(varbinary,au_lname) = convert(varbinary,'Green')

Near Real time updating of fact table

-what is the best method that to implement the near real time updating of a
fact table without affecting all those users who are reading from it?
-fact table in question is only 1.8gb in size, 3.1 million rows, expect near
real time updating of approx 1000 new fact table records every 15 min,
occassionaly updates of historical (modified) fact table records - this is
where i assume write locks v read lock conflict may occur
- i am only concerned for the crystal report users who are using the fact
table via sql views ie relational db, the cube at present can stay as write
an night and read during the day
- any help greatly appreciated
U can use DTS for this, keep in mind that DTS is version sensitive.
Message posted via http://www.sqlmonster.com
|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment and very
curious to find out how.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>
|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>

Near Real time updating of fact table

-what is the best method that to implement the near real time updating of a
fact table without affecting all those users who are reading from it?
-fact table in question is only 1.8gb in size, 3.1 million rows, expect near
real time updating of approx 1000 new fact table records every 15 min,
occassionaly updates of historical (modified) fact table records - this is
where i assume write locks v read lock conflict may occur
- i am only concerned for the crystal report users who are using the fact
table via sql views ie relational db, the cube at present can stay as write
an night and read during the day
- any help greatly appreciatedU can use DTS for this, keep in mind that DTS is version sensitive.
Message posted via http://www.droptable.com|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment and very
curious to find out how.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>

Navigation buttons

Is there a way to add the navigation bar with next page, previous page etc buttons at the bottom of the report. If the report is long the users dont want to scoll up to click on next page.

There is no easy way to accomplish this. It can not be done in Report Manager. If you are writing your own web app you could do this by writing your own tool bar, but it would require quite a bit of code.|||Thank you Daniel.