How to create an itinerary DB?

[merika]

Hi all,
I´ve been reading and reading but it´s been some time since I´ve worked with Access, SQL, etc.

I´m trying to create a DB which in my eyes is pretty complicated but should be do-able, somehow i just get stuck in my thinking proces and i could surely need the input of people with way more experience.

The Db I need to create should give the people at my office a good search functionality for looking up when a boat starts a cruise, how long that cruise is (varying from 4 - 16 days), which sites it will visit (multiple sites per day), what the capacity of the boat is (max passengers), if it can be usd for diving or not, if it is in maintenance or not.

A (hopefully) quick rundown of what I have so far:
Table 1: Boats
BoatName - PK (102 boats in total)
CapacityID - FK
ClassID - FK
TypeID - FK
MaintenanceID - Yes/No - FK
DivingID - Yes/no FK

Table 2: Diving
DivingID - PK
Diving - Yes/No

Table 3: Maintenance
MaintenanceId - PK
Mainentance - Yes/no

Table 4: Capacity
CapacityID - PK
Capacity

Table 5: Class
ClassID - PK
Class

Table 6: Type
TypeID - PK
Type

Table 7: Dates
SailDate - PK

Table 8: Sites
SiteID - PK
Land
Place

Table 9: Days
DaysID - PK
SailingDays

I just have no idea at the moment (okay, for the last week) how to connect everything together. How can I make this all work together so that I can have my colleagues search for a boat that sails on a certain day of the week (i.e. Friday), a certain date, for a certain amount of time abd/or with certain sites to visit? To make it more complicated, all boats have various routes they travel and of various lengths, leaving on various days of the week

I understand if my question is unclear but any help is really appreciated

 

[MarkK]

Don't use the boat name as the primary key. Add an Autonumber field.

Don't link to data when there is only a one-to-one relationship. A boat only has one capacity, so put that capacity directly into the boat table. Same for diving, and maybe others.

tBoat
BoatID (PK)
BoatName (text)
Capacity (Numeric)
IsDivingPlatform (Boolean)
...

You'll need a trip table in which you define, well, ahh, trips.

I would define/express Maintenance as a trip, not as a scalar attribute of a boat. Maintenance is a place a boat can go with a start date and an end date, lust like a trip.

You need a destination table. A trip will be composed of destinations.

Never store dates all by themselves in a table. A date is always a single dimension of some other complex object. A person object has a birthday dimension, a trip object has a start date dimension, a booking object has an arrival date dimension.

There're a few observations. Does that stuff make sense?
Mark

 

[merika]

Hi Mark,

It sure does make sense! Thanks so much for getting me out of my thinking game and see it a different way!

I´ll get started on this and if I run into other issues, I´ll be sure to post back

Think this is exactly what I needed to get started again...

Just one thing though...

There are more boats that have the same capacity, so wouldn´t it be better to have capacity seperated in order to have it only stored once?

 

[MarkK]

There are more boats that have the same capacity, so wouldn't it be better to have capacity separated in order to have it only stored once?

No, the power of a database arises from its capacity to store one-to-many relationships between objects, and capacity is not an object, it is but a single dimension.

Imagine a Person object that has LastName, Address, and PhoneNumber dimensions. Maybe you have multiple people with the last name Smith. Then Edna Smith gets married and you change her last name to Jones, only in doing so you change all other linked Smith's last name to Jones.

OK, so never link a single dimension of data to save space. A single dimension of data inexplicably belongs to the object it describes, and if you have data that repeat, this is an important feature of your data that you want to preserve!!!

Still making sense?
Mark

 

[MarkK]

And welcome to the forum.