homepage Welcome to WebmasterWorld Guest from 54.204.215.209
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
What is the best design?
m8fyu




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

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
id
departure_date
departure_time
departure_port
arrival_date
arrival_time
arrival_port
trip_description
trip_days
trip_nights
fk_boat

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.

 

topr8




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved