table layout for airport minicab quote system

[antonyx]

ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..

you can see a similar quote system used on the following website..
http://www.londonairporttaxi.co.uk

there will be three main tables..

Airport Table
airportID - primary key, autonumber
airportNAME - text

the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton

Town Table
TownID - primary key, autonumber
TownNAME - text

the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)

Car Table
CarID - primary key, autonumber
CarNAME - text

the car table will consist of 4 records..Saloon,Estate,MPV,Executive

now what i need to do is create a similar quote system like there is on the url above..

my original idea would be to do something like this..

Have three tables that contain the quotes for each type of car..the first two listed here will work i think..


Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency

Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

however.. for airport to airport quotes.. the resembling table would look something like this..

Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency

but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)

also one example may be Heathrow to n1 is £30... this does not mean however that n1 to heathrow will be £30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..

anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.

thanks in advance for any help.

[neileg]

You only need one table, the pickup and destination points will define the nature of the journey. I don't understand the bit about not holding the same airport ID twice. Why not, it's a foreign key in this table? And would you want cascade deletes in this setup anyway? You're going to demolish Heathrow?

Is there no link between the fares for different vehicles? Say, the estate is 110% of the saloon fare, the MPV 175%, the executive 200%?

[antonyx]

ok could i do this then..

have one quote table..

quote id
pickup
destination
car id
price

and then place either the town or airport ids in the pickups or destinations??

how would i got about linking the tables in this way?

[neileg]

Probably a combo box is the most suitable on a form. If you use the combo box wizard it will guide you to the right answer. Base the combo on a query, not directly on the table, so you can apply a sort to the data in the query.

[antonyx]

ok, i think i get you..

i place one combo on the form regarding the car type

ill place 4 combos on the form regarding locations.. (2 for pickup and 2 for destination)

and then shall i literally base them on a query, which just includes the field i want to select from?

[neileg]

Originally Posted by antonyx

ill place 4 combos on the form regarding locations.. (2 for pickup and 2 for destination)

I don't understand. You only have one pickup and one destination, why two pairs of combos?

and then shall i literally base them on a query, which just includes the field i want to select from?

You should hold the PK of the table, not the name of the destination. So you need two fields. The first column will be the PK but this will be invisible to the user. The combo box wizard will guide you to the right answer.

[medway]

Hi there,
Can you please tell me what happened to the engine & how to get it now?
Thanks.
Y

www.door2doorcabs.com

[DCrake]

Some things you seemed to have overlooked is the need for additional pick ups, special passenger requirements, Luggage Excess, time of day, ETA, Etc. All these factors need to be taken into consideration when calculating a fare. A straightfoward 1 person 1 destination no additional needs would give you a base price but then you will need to factor in all the above parameters. Not quite as straightforward as it first seems.


David