I need some advice

[octogon]

Hello everyone,

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 :

login,password,firstname,lastname,address,...

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"
month
day

it should be possible to get a discount depending on the age of the participant, so I created the following table :

excursion_discounts :

excursion_id
start_age
end_age
discount

the shoppingcart system consist of following tables:

pending_ordered_excursion:
(stores information about ordered excursions)
session_id
excursion_id
date
amount of adults

pending_ordered_excursion_amount_children:

(stores information about the amount of children in order to calculate the price)

refers to pending_ordered_excursion:
session_id
excursion_id
date

refers to excursion_discounts:
excursion
discount_id

amount_of_children

after confirming the order: the information is moved to tables:

order :

order_id auto_increment
user_id
billingaddress,...
total

ordered_excursion:

order_id
excursion_id
date
amount_of_adults
price

ordered_excursion_participant:

order_id
excursion_id
date
firstname
lastname
age

I hope u can tell me if what I am doing is the right way. Thanks in advance

[davidcairns]

1. If you are concerned about a security issue with authentication details you should limit access to the user table to the authentication and new user routines and use a view which exludes these fields for all other access.

2. It is usually better to use a user type, but that is not an absolute, it depends on the situation.

3. Change the fields month and day in teh excursion table to be a single date field (which can include time)

Aside from that I would say try and draw this as an ER diagram (personally I use a pencil and a large piece of paper)
and see if it looks good

Cheers

D

[Stylish]

I would also move pending_ tables into a column within the user table, with values 0 or 1. This is assuming the pending_ tables do not have any additional columns.

[jbennet]

What DBMS will you use?