Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

What is the best design?



9:52 am on Apr 2, 2010 (gmt 0)

5+ Year Member

I've got some data that I'd like to incorporate into a database but I'm not sure of the best approach / design to do so. I am newbie when it comes to MySQL and although I do understand some fundamentals such as datatypes, entities, relationships etc. I don't have the necessary expertise to design my table correctly.

My site is all about deep-sea fishing trips. I have a number of boats and each has various scheduled itineraries throughout the year. So each boat can have many schedules and i'd like to add a schedules table too (i'm currently hand coding each schedule). my future plan is to incorporate a search facilty so that customers can search for a schedule based on their preferred departure date.

So my schedule table will have the following data

Am i right in thinking I ust store all this inside one table? Each boat has runs about 50-100 trips per year. If I have 100 boats thats potentially 10,000 records. Does this seem a reasonable design? Id prefer to get some feedback before I go ahead with the build as entering all the info will take me some considerable time.

I appreciate any help and advise offered.


5:42 pm on Apr 2, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

looks good to me, i assume the Arrival and departure port fields would be a numeric field that references another table 'ports'
also i assume the fk_boat field would be numeric and would reference another table 'boats' (assuming you might have data about the boats that may be pertinent, size, number of fishermen etc)

if the same trips are also being repeated, you might want to have another table trips which holds departure_port,arrival_port,trip_description etc and reference it in your schedule table witha field like trip_id

Featured Threads

Hot Threads This Week

Hot Threads This Month