[RESOLVED] Which to learn, Access, Sql, etc.

I've made a program that opens up "Text Files" and I use them as my Database/Saved information.

The program is a Video/DVD rental program.

It keeps track of Customers, Accounts, Actors, DVD's, Fees, etc.

Right now each movie saved to the text file is about 1.2k (550k total)


I expect to have 200-700 Accounts with double to tripple the customers.
I expect to have 700-1500 DVDs total.

I know little about Access, little about Sql. Nothing about any other Database.

Some of the information that is stored for Movies is following:

VB Code:

Type movieinfo
   moviename As String 'example ---Main Hoon Na
   Character1 As String  'example ---Shah Ruk Khan
   Character2 As String  'example ---
   releasedate As String  'example --- 01/01/2004 (sometimes you just know the year)
   RunningTime As Integer 'example --- 180 as mins
   AgeGroup As Byte  'example --- 0=All Audiances,1=adults,2=teens+,3=kids+
   Subtitles As Boolean 'example --- 0 = False, if it was true. -1 = True
   NewMovie As Boolean 'example --- -1 = true , 0=false
   Description As String 'example --- Maj. Ramprasad Sharma(Shah Ruk Khan) is going back to school...
   Type As Byte 'example  --- 0-10 (0=comedy, 1=drama, 2=action/suspence, 3=scary/horror, 4=family,5=Science Fiction)
   quantity As Byte 'example --- 2 in stock
   Avail As Byte 'example --- 1 avil, meaning that 1 is checked out
   movienumber As Integer 'example --- 75, but -75 if it is deleted
   Deleted As Boolean
   RentedNew As Integer
   RentedOld As Long
   AccountsRentedTo As String
   BarCode As String
   PicPath As String 'Hard Drive Path to the Picture to display.
   TotalQInfo As Long
   QInfo() As QuantityInformation
End Type
 
Type QuantityInformation
    CopyLetter As String 'A,B,C,D,E....AA,BB,CC......AAA,BBB....
    Rented As Boolean 'self explanitory
    Account As Long '5 - know who rented it out to.
    PurchaseDate As String '07/16/2006
    PurchaseAmount As Currency '17.99
    SoldDate As String '07/17/2006
    SoldAmount As Currency ' 5.99 (Add in cost of renting it out 17, total from movie 22.99 that is $5 profit)
    SoldTo As Long ' Customer Array Number
    RentedNew As Integer '5   (so 3*5=15)
    RentedOld As Integer '1   (so 2*1=2)
    RentedNewProfit As Currency
    RentedOldProfit As Currency
    LateNew As Integer 'tracks how many times a movie has been returned late as new
    LateOld As Integer 'tracks how many times a movie has been returned late as old
    LateNewProfit As Currency
    LateOldProfit As Currency
    Status As Byte '0=Available/Owned still,  1=Sold, 2=LostByCustomer,3=Damaged, 4=stolen, etc.
    NewWhenRented As Boolean
End Type

I just copied and pasted my current User Defined Type.

I am going to first learn about a specific database format/system. Then Re-program my DVD Rental program with it.

Thank you for your input.

Database is depending on the project on a little level you have to use Access, after SQL server or Oracle. But about Learning I suggest that Learn all but perfect ness is necessary. Increase the knowledge of SQL.

From a VB point of view, there isn't much difference between database systems - it is typically just one line of code (which specifies connection details) that needs to change from one system to another. From the sound of it Access will be fine for you, or you could use SQL Server Express (which is free) for more reliablility.

I would recommend going thru our ADO Tutorial (link in my signature), as that shows how to do the most common tasks.

You have a good data structure already, but it can be improved for use in a database. For example, with "AgeGroup" you would add an extra table containing the values & descriptions (0=All Audiances,1=adults,...), and could easily show this on your VB form directly from the database (which means that if one of these descriptions changes, you only need 1 change in the database, rather than several in a program which then needs to be re-compiled).

I did the first tutorial, i'll see if there is another one. I created an Access database, or started to. I made a crap load of tables to show relations, I'm not sure if i'm using the tables correctly. I've uploaded and attached the database I started. I have been told (which may be wrong) that if you can design a database (tables relationships etc) it can reduce coding drastically, and even help eliminate problems. If possible, could someone tell me if i'm on the right track with what i've done? Thank you.

I created a bunch of dependancies and about 20 or so tables.

Am I linking this information correctly?

Good day!!!


From my point of view, you have done a great job. Your next step is to check whether your tables are normalized. 3NF/4NF/5NF depending on the needs of the company. Next is to be familiar with the forms/queries/reports needed for the video rental. Then comes the coding part. You may use the visual basic editor for access. ADO can also be used.

I have designed systems using access alone, but as requirements grow, you need a stable DBMS like Sequel server/MySQL and etc. You may also need VB as the front end.


Greg :) :) :)

That design looks very good considering your lack of DB experience.. the only comments I have are:

In "ActorsInMovies" you have a "Priority" field.. the values you have specified in the comments should be in a separate table ("ActorPriorities"?), so they can be used as I described above. The same applies in other places too, such as "Paidwith" in "CheckedOut".

In your "Invoice.." tables you have "Total" fields.. if this is just a sum of other fields (eg: Price and Shipping) then you dont need to store the total - it can be calculated for you when you get the data from the database (by specifying it in your Select query);This removes the chances of errors when only one of the fields is updated. Various fields in the "Movies" could also be done in a similar (but slightly more complex) way, such as "Available" and "Rentednew" etc (which would be a calculation based on the data in "RentedInfo").

That makes sence, repeating data, or saving information that can be calculated seems it could be an issue. Thank you all for your inputs thus far. Some of the fields in the tables I may be removing. But looks like I need to make sure I clean up things that can be seperated into there own tables. Thanks agfain for the input guys.

After re-revewing my tables, I can see a few other places where I need to move fields from one table to another.

When I created the database I didn't understand the Relationships much.

Like in Awardinmovies I have Yearofaward, specifics, extrainfo. I Belive I need to move those into the Award section.

I honestly can't remember what I created that Priority Variable for. I think maybe to show the lead actors first, etc....


EDIT:

Do I need to create a "look up table" for CustomersonAccounts, to link the customers with the accounts table? Or does the account number in the customer table work fine?

For Award/Awardinmovies, it depends what info you are storing.. I had assumed that the Award would be for general details about the type of award (eg: Film Festival Award for Best Actress/Awarded by the film federation), and that Awardinmovies would be for details of specific 'wins'. If my assumptions are correct, your current design is right.

The idea of Priority is a good one - I have been searching for movie as an xmas present, and wanted to know if a certain actor was a major part or not (luckily one of the web sites I looked at had that info). I presume the customers would want to know this too.

The need for CustomersonAccounts depends on the number of accounts a Customer can have.. if each Customer can have multiple accounts, you need it (and remove accountnumber from the customer table). If a customer can only have one account (and each account has only one customer) there isn't actually a need for the account table - the details should be moved into the Customer table.

I'm allowing up to 5 customers per account. up to 10 movies rented to an account at a time also.

Happy holidays to all.

Thank you again Si_The_Geek.