I need tips on my database schema... will paypal lunch whoever has best review

[jonaofarc]

I need tips on my database schema... will paypal lunch whoever has best review

 

[attachment deleted by admin]

[jonaofarc]

does it follow 1F 2F 3F , how does this schema look, it based to be used in a movie site where one can search by actors or scenes of movies

[grissom]

Hi Jonaofarc

 

Looks like you've covered most of the bases so far, but to be more in depth I'd like to know what purpose the database will be used for.

 

If it for a video/DVD rental business, then you will need to have a table to store who took out what video, what date they took it out, when they brought it back, did they bring it back on time, did they pay, was there a penalty for late return etc etc.

 

If it is for a rental business where money is transacted then there will obviously need to be tables to store all the financial side of the business, who has paid, who is owing, etc.

 

Finally, for fields which will have certain defined values like "type of video", do you have a system in place to avoid spelling mistakes (a common pain in mysql tables) for example, a table would class "HORROR" and "HOROR" [spelling mistake] as two separate groups.  If you are programming a drop-down menu to input the values, then this is taken care of, otherwise you will need a lookup table between video_type_id and video_type_name  (excuse the pseudocode)

 

I also didn't see a field for "comments" a TEXT field where you can enter any spoken or written comments made about a particular film.

 

If you give me a bit more info about how the db will be used, I'll be able to give it some more thought.

 

All the best !

[aschk]

The one thing the diagram seems to be missing here is relationships, i.e. 1-> many, 1->1, many -> many. This isn't clear. I'm guessing that (for example) vid to video_by_category is a 1-> many (i.e. 1 video can be in more than 1 category). Alter your diagram to show these relationships.

[Mchl]

You have type mismatched for foreign keys (video_id is integer in vid, but char invideos_by_category)

I have no idea how 'actors/actresses' is related to 'vid' (on actor_id to video_id? wtf?)... oh wait... those lines in the picture don't represent actual relations... so why are they there?