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.
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