NEED HELP and GUIDANCE!

**David Portas** · Jul 19 '05, 09:55 PM

Re: NEED HELP and GUIDANCE!

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,M emberstatus));

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 ;
[color=blue]
> 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[/color]

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

**Simon Hayes** · Jul 19 '05, 09:55 PM

Re: NEED HELP and GUIDANCE!

dahct@yahoo.com (new_GUY) wrote in message news:<c978d8cf. 0406131603.34ed ddc9@posting.go ogle.com>...[color=blue]
> 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...[/color]

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

**Leythos** · Jul 19 '05, 09:56 PM

Re: NEED HELP and GUIDANCE!

In article <c978d8cf.04061 31603.34edddc9@ posting.google. com>,
dahct@yahoo.com says...[color=blue]
> 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...[/color]

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@rro hio.com
(Remove 999 to reply to me)

NEED HELP and GUIDANCE!

NEED HELP and GUIDANCE!

Comment

Comment

Comment