3 Replies - 15434 Views - Last Post: 10 December 2009 - 01:11 PM Rate Topic: -----

#1 kuneho0615   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 02-December 09

help with my erd for video rental database

Posted 08 December 2009 - 06:38 AM

i am doing a simple database for video rental using access. im currently confused on how my ERD should look like. so far what i have is this:

CustInfoTBL:

custID pk
Lastname
FirstName
Address
ContactNum
ContactEmail
DateJoined


TransactionTBL:
custID
TransID pk
DateRented
DateDue


VideoInfoTBL:
TransID
VideoID
title
Genre
Price
Days
InStock

I dont know if i have my ERD correct. what i want to do is a simple rent in and out of videos that also saves a report of past rental histories.

thanks

Is This A Good Question/Topic? 0
  • +

Replies To: help with my erd for video rental database

#2 ForcedSterilizationsForAll   User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: help with my erd for video rental database

Posted 08 December 2009 - 08:02 AM

ERD?

Earned Revenue Display?

I've worked at 5-6 different video stores and all of them had really crappy UIs.
Was This Post Helpful? 0
  • +
  • -

#3 MentalFloss   User is offline

  • .
  • member icon

Reputation: 577
  • View blog
  • Posts: 1,500
  • Joined: 02-September 09

Re: help with my erd for video rental database

Posted 08 December 2009 - 11:22 PM

Quote

ERD?

Earned Revenue Display?


It's an entity relationship diagram. (I don't know if you were joking, but regardless, I cleared it up for anyone else too)

Here's my crack at it:

Quote

Customers
ID INT NOT NULL PK [IDENTITY]
FirstName VARCHAR(50) NULL
LastName VARCHAR(50) NULL
Address VARCHAR(50) NULL
ContactID VARCHAR(50) NULL FK -> CustomerContactInfo.ID

=================================================================================

CustomerContactInfo
ID INT NOT NULL PK [IDENTITY]
CustomerID INT NOT NULL FK -> Customers.ID
PhoneNumber VARCHAR(50) NULL
EmailAddress VARCHAR(50) NULL

=================================================================================

Transactions
ID INT NOT NULL PK [IDENTITY]
CustomerID INT NOT NULL FK -> Customers.ID
VideoID INT NOT NULL FK -> Videos.ID
RentedOn DATETIME NOT NULL
DueOn DATETIME NOT NULL
ReturnedOn DATETIME NULL

=================================================================================

Videos
ID INT NOT NULL PK [IDENTITY]
Title VARCHAR(100) NOT NULL
Price DECIMAL NOT NULL
GenreID INT NULL FK -> Genres.ID
InStock BIT (?) NOT NULL

=================================================================================

Genres
ID INT NOT NULL PK [IDENTITY]
Description VARCHAR(MAX) NOT NULL


Sorry for the formatting... I tried.

Now, onto my decisions:

1. I think contact info belongs in its own table so you can expand information on it more easily. You might find that you want to keep more than just phone and email and now you can without disrupting your customers table.

2. Transactions - I guess that this depends on whether you want a transaction to be updated or an entirely new one inserted to update the status. But as I see it, you can group on customer and update those records. With this, you create the entire transaction and later update the returned on date.

3. Genres is going to have a lot of repeats. So, I think it belongs in its own table with a key reference.

Maybe this looks ok to you, maybe not. I must say DB design is not my specialty. This was just practice.

I'd say give it a try and when it turns out it doesn't work, revise it.
Was This Post Helpful? 0
  • +
  • -

#4 ForcedSterilizationsForAll   User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: help with my erd for video rental database

Posted 10 December 2009 - 01:11 PM

View PostMentalFloss, on 8 Dec, 2009 - 11:22 PM, said:

Quote

ERD?

Earned Revenue Display?


It's an entity relationship diagram. (I don't know if you were joking, but regardless, I cleared it up for anyone else too)

Here's my crack at it:

Quote

Customers
ID INT NOT NULL PK [IDENTITY]
FirstName VARCHAR(50) NULL
LastName VARCHAR(50) NULL
Address VARCHAR(50) NULL
ContactID VARCHAR(50) NULL FK -> CustomerContactInfo.ID

=================================================================================

CustomerContactInfo
ID INT NOT NULL PK [IDENTITY]
CustomerID INT NOT NULL FK -> Customers.ID
PhoneNumber VARCHAR(50) NULL
EmailAddress VARCHAR(50) NULL

=================================================================================

Transactions
ID INT NOT NULL PK [IDENTITY]
CustomerID INT NOT NULL FK -> Customers.ID
VideoID INT NOT NULL FK -> Videos.ID
RentedOn DATETIME NOT NULL
DueOn DATETIME NOT NULL
ReturnedOn DATETIME NULL

=================================================================================

Videos
ID INT NOT NULL PK [IDENTITY]
Title VARCHAR(100) NOT NULL
Price DECIMAL NOT NULL
GenreID INT NULL FK -> Genres.ID
InStock BIT (?) NOT NULL

=================================================================================

Genres
ID INT NOT NULL PK [IDENTITY]
Description VARCHAR(MAX) NOT NULL


Sorry for the formatting... I tried.

Now, onto my decisions:

1. I think contact info belongs in its own table so you can expand information on it more easily. You might find that you want to keep more than just phone and email and now you can without disrupting your customers table.

2. Transactions - I guess that this depends on whether you want a transaction to be updated or an entirely new one inserted to update the status. But as I see it, you can group on customer and update those records. With this, you create the entire transaction and later update the returned on date.

3. Genres is going to have a lot of repeats. So, I think it belongs in its own table with a key reference.

Maybe this looks ok to you, maybe not. I must say DB design is not my specialty. This was just practice.

I'd say give it a try and when it turns out it doesn't work, revise it.


You may actually want to set something up to easily allow multiple copies of the same movie. So you'll have the Title information (anything pertaining to the movie that you want which if you want to allow searching of titles you'll want to break down into more tables), and then have a table for Inventory. This way you can give each copy it's own barcode and can keep track of if it's for rent or for sale.

The Inventory table would also prevent having to keep entering the same information if you were to get... say 100 copies of the same movie that no one will rent two weeks after release.

I hope this is just for you to fart around with or is for a school project as opposed to something which would be used in a production environment. If it's a production environment, you probably don't want to use Access at all and probably would want a web-interface (which could also allow someone to search the movie database and then reserve the movie by calling the store).

This post has been edited by ForcedSterilizationsForAll: 10 December 2009 - 01:14 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1