I am currently developing an excursion booking system and I am looking for some advice on some matters of database design.
At the moment I have the following tables in my system :
a table 'user' which holds information such as :
Q: is it better design to store authentication credentials in a seperate table with a link to 'table user'?
Travel Agencies should also be able to log in to the system, in order to input excursions :
so I created a seperate table 'agency' which holds the same fields as table 'user' except for 2 fields:
( I also have two tables called : pending_user and pending_agency)
Q: is it better design to merge tables user and agency and create a new table 'usertype'?
the dates on which an excursion takes place I store in a table which looks like this:
excursion_id refers to table "excursion"
it should be possible to get a discount depending on the age of the participant, so I created the following table :
the shoppingcart system consist of following tables:
(stores information about ordered excursions)
amount of adults
(stores information about the amount of children in order to calculate the price)
refers to pending_ordered_excursion:
refers to excursion_discounts:
after confirming the order: the information is moved to tables:
I hope u can tell me if what I am doing is the right way. Thanks in advance