Looking for some direction/help on a personal project

T

tgabe213

2[H]4U
Joined
Aug 27, 2007
Messages
3,684
Hope you guys aren't getting sick of me, yet at least :)

Here's what I'd like to do:
Have a database that contains all of the information about the DVD's I have. I'd like to store various things like name of movie, length, format (dvd/bluray), studio, date filmed.

  • I'd like to create a small web app that will allow me to do various things with this information. This will be done in asp.net with focus on c# (and not vb).
  • I'd like to first be able to display the information in the database, and have the option to sort based on the different categories (ascending/descending).
  • I'd like to be able to have a drop down box that lets me select either bluray or dvd's, and have that list displayed.
  • I'd like to be able to modify (as well as add/delete) an existing movie with my aspx page, without having to go into the database
  • Have the output display no more than say 15 titles per page, and give me an option to move to the 2nd page

What I'm really looking for here is how I should set this up. I think I'll eventually be able to figure most things out for displaying the data (I hope). Based on my last thread, I'd like some input on the database as well.
  • Since I want to have just bluray or just dvd's show, should I have a separate table that contains this information for the dropdown menu?
  • Should everything be in 1 table, or all broken up?

It's decently late so I may be forgetting a thing or two. Though it may seem like a lame idea (DVD's), I think this is going to be a good starting tool to learn as much as I can. I think eventually the updates could be used using LINQ.
 
No one's biting?

tgabe213 said:
I'd like to be able to modify (as well as add/delete) an existing movie with my aspx page, without having to go into the database

Have the output display no more than say 15 titles per page, and give me an option to move to the 2nd page
Click to expand...

You'll love the built in web controls. GridViews and ListViews include automatic paging and inline record insertion editing and deletion.

tgabe213 said:
Since I want to have just bluray or just dvd's show, should I have a separate table that contains this information for the dropdown menu?

Should everything be in 1 table, or all broken up?

I'd like to be able to have a drop down box that lets me select either bluray or dvd's, and have that list displayed.
Click to expand...

Break it out, definitely. This ensures consistency, prevents typos, and makes filtering and grouping code much cleaner.

The more you break it up within reason the more you'll be able to do with your data. It makes filters (picking just DVD, or Blueray, or whatever the next movie media is) trivial.
 
Thanks for the reply! (yea, no one is biting).

How should the tables be broken up? That's my biggest concern. Should I have a separate table for each category? So the Length table will have an ID and the time, and that's it?
 
I'm looking to learn, and fulfill a need at work at the same time. The DVD concept it pretty identical to what I am looking to do for work.
 
honestly, for the information you've listed, i dont see any point in breaking the tables up, i'd just add an extra field with the type of media. extracting bluray/dvd/both entries would be a trivial, and i think all-around it is a simpler model to work with. it also scales a little better (say you wanted to add video/vcd/laserdisc extries in the future, you'd have to create a new table if the media type didnt match an existing one).
 
tgabe213 said:
Thanks for the reply! (yea, no one is biting).

How should the tables be broken up? That's my biggest concern. Should I have a separate table for each category? So the Length table will have an ID and the time, and that's it?
Click to expand...

Unless I have completely misunderstood what is going on here I would absolutely advise agaisnt create seperate tables for each DVD type.

I would create the following tables:

DvdArchive
  • id [int]
  • CategoryId [int]
  • Name [varchar]
  • ..
  • whatever other fields you need
  • ..

DvdCategories
  • id [int]
  • Description [varchar]

When you insert a new entry into DvdArchive you would set the value of DvdArchive.CategoryId to match the DvdCategories.id, standard relational stuff.

So you would have:

DvdCategories
  • 1 | Blu Ray
  • 2 | Old School
  • 3 | HD Dvd

DvdArchive
  • 1 | 1 | Bluray Film 1
  • 2 | 1 | Bluray Film 2
  • 3 | 2 | Old School Dvd Film 1
  • 3 | 3 | HD Dvd Film 1
  • ..
  • and so on and so on and so on
  • ..

So if you only want to get BluRay films you would do:

SELECT * FROM DvdArchive WHERE CategoryId = 1

If you create a different table for each category 6 months from now when BluHDRayTurbo format comes along you are going to have to mess around. Using the method described above you can simply create an admin area where you can add/edit categories at will without having to change your DB structure.
 
fluxion said:
honestly, for the information you've listed, i dont see any point in breaking the tables up, i'd just add an extra field with the type of media. extracting bluray/dvd/both entries would be a trivial, and i think all-around it is a simpler model to work with. it also scales a little better (say you wanted to add video/vcd/laserdisc extries in the future, you'd have to create a new table if the media type didnt match an existing one).
Click to expand...

Maybe I'm not as well versed with database design as I think I am, but having one table for ALL the data does not seem flexible at all. Of course if this is a super simple app that you never plan to expand, than it would probably work just fine for you.

I am actually working on a similar application myself. I've only been white boarding out ideas and design decisions for it so far. I won't go into the exact structure of my DB, but it goes a little like this;

Code: 
movies
   imdbid
   name
   genres
   castandcrew
   ... more details

users
   userid
   username
   ... more details

genres
   genreid
   name

castandcrew
   imdbid
   name

moviestate
    imdbid
    userid
    state
    ... more details

Some information I store in a config file as well, instead of a table (color information, type of disc (dvd, bluray, vhs) etc. Basically information that will only be changed once in a blue-moon, and probably never be changed.

Though it looks like I'm storing a ton more information than you as well. So it makes sense for me. One problem I'm having right now is the best way to store what type of disc it is - without duplicating data if both a DVD and Blu-Ray are owned of the same movie. Besides that, I feel I have a really solid DB design.
 
tgabe213 said:
How should the tables be broken up? That's my biggest concern. Should I have a separate table for each category? So the Length table will have an ID and the time, and that's it?
Click to expand...

What BuddhistPunk said is pretty spot on.

I was a bit overzealous in how I talked about breaking things up. What you want to do is identify the different things that you're going to track. Each "thing" should have its own table.

Taking a look at the short list of stuff that you listed (name of movie, length, format (dvd/bluray), studio, date filmed) I would break it up into three things.

Movies, Disc Formats and Film Studios.

A movie has a name, a length and a date filmed, and contains references to the format that it uses and the studio that produced it.

A disc format has a description and a whatever other information you care about.

A studio has a name, an address and/or whatever other information is important.

Breaking things out like this means that you maintain consistency across entries (Blue Ray is always spelled the same for every entry) and you make it easy to make changes (every Paramount movie will contain a reference to Paramount, so if the studio address changes it you just update it in one place)

(let me also chime in that the way I go about designing databases is heavily influenced by how I apply object oriented design, so, take it with a grain of salt)
 
Thanks for all the input everyone. The time has come to start the project that is similar to the one here, but for work. Should I make a new thread for input, or just add in to this one (the content is a little bit differen't)?
 
Is the input your looking for related to DB design? If so - might as well keep it in here... I guess..
 
Yea, it's pretty much the same thing. Employees input investment referral information to the database, and that gets displayed and sorted as you wish.

Here's the design I came up with:
Referrer
  • Id
  • Name
  • LocNum
  • Date
  • PhoneNum


Customer
  • Id
  • Name
  • Address
  • City
  • State
  • ZIP
  • SpouseName
  • TimeToCall
  • HPhone
  • WPhone


PostReferral
  • DateMetWith
  • SaleMade
  • RevenueGenerated
  • ReferrerPaid
  • Qualified
  • FinancialAdvisor


Additionalnformation
  • Id
  • ExistingRelationships
  • AdditionalServices
  • Comments
Click to expand...

Don't worry. I'll end up working on both projects simultaneously between work and home.
I also think I found my method for displaying the data in the database. These datagrid's for examples, etc.
 
I just want to have a good database design with good flexibility, that easily allows me to sort how I'd like, with good performance.
 
I'm having a hard time with a select statement (on 2 simple tables, simulating what I will do for this project). Basically, our locations are broken up into Brick (B&M) and Pick (Grocerystore). I want to have a dropdown box that will either Bricks or Picks (I'd like to have an all selection, if possible too).

So my tables are:
Referrer
  • id
  • Name
  • LocNum
  • Date
  • PhoneNum

&

BranchDropDown
  • id
  • LocNum
  • LocName
  • LocType

LocType will have values of either Brick or Pick. I think I need to use a join statement? I'm trying different things out based on examples, and trying the query builder in VWD, but I'm not having much success. The values of Referrer table are input by the user with a form, and BranchDropDown is populated once, with all locations, by me.

Thanks...

Edit: Or would it be easier to just add branch type to the add form, and I can use
SELECT DISTINCT gv_state
FROM gridview
WHERE (gv_state IN ('B', 'P')) as my select statement.
 
You must log in or register to reply here.
Back
Top