CAR HIRE

[salim]

I am trying to design a web base system for A1 Holloway car hire, I am not sure about my Entity Relationship diagram as when I try to make the relationship between two table it never goes the way, here it is:

booking_tbl ( PK booking_id, FK vehicle_reg, FK customer_id, booking date, booking duration)

vehicle_tbl ( vehicle_reg, vehicle coulour, vehicle make, vehicle)

relationship between two tables is as follow
booking_tbl m: 1 vehicle_tbl ( many to one)

but it shows the opposite ? would that be because booking_tbl has a compound PK key ( more than PK and some FK)

Note that I haven't included the other table ( customer )as i only have the problem with this relationship between those two tables.
thanks

[trudge]

These don't look like any ER diagrams I've seen, but anyway.

What do you mean by 'it shows the opposite'?

What SQL query have you used to show you anything? YOU make the relationships between tables, not some diagram.

[salim]

when it shows the opposite it means instead 1:m then it's m:1 anyway i have that sorted, talking about the e-r diagram you said that you have seen nothing like it before? well I don't know how to explain it further or illustrate it better but I am using ms access and this is how tables are linked together

[salim]

Hi again,
I think my problem is so complex, I have been working on it and I came up with some solutions, now my question is as follow:
I try to establish a 1:m between two entities or table ( vehicle_tbl and Booking_tbl) but I cannot, i get the error message of not finding the index key, anyway here are the tables, I checked all the attirbutes and matching data to make sure there is no data missing

vehicle_type_tbl ( vehicle_type PK...ect all the rest of attributes)
vehicle_tbl ( vehicle_reg PK, vehicle_type FK, ....ect...attributes)
Booking_tbl (Booking_id PK, vehicle_reg FK, Customer_id FK...ect...all the rest of tributes)

would that be because the vehicle_tbl has a compound keys ? and has already a relationship with 1:m end with vehicle_Typetbl? that's why i cannot establish 1:m with Booking_tbl ? if it's the case then how would I mark the vehicle_type ?

[trudge]

You mention that you are using MS Access to do this in. So you have 2 learning curves on your plate: how to use Access and how to design a relational database.

Access is the front end to the MS Jet database engine. It doesn't do a very good job of teaching you how to design relational databases.

Second, you mention that this is going to be a web-based application. I strongly recommend that you NOT use MS Access for this application. It is not designed for multi-user / multi-threaded usage.

To better understand relational database design, install MySQL client and server (it's free) and any decent host nowadays has it already installed.

Then get yourself a good book on relational database design. In other posts I've mentioned 3 that I use on a regular basis: 'Database Design for Mere Mortals' by Michael Hernandez; 'SQL Queries for Mere Mortals' by the same author; 'MySQL' by Paul DuBois.

[HB25]

Hi salim
Have a look at this link ( http://www.databaseanswers.org/data_models/car_hire/index.htm ) you may find it useful.
Best wishes
HB25

[tesuji]

hi salem,

checking your schema

booking_tbl ( PK booking_id, FK vehicle_reg, FK customer_id, booking date, booking duration)

vehicle_tbl ( vehicle_reg, vehicle coulour, vehicle make, vehicle)

I can assert that you did a good job. The relationship booking_tbl M:1 vehicle_tbl is correct because:

1. the primary key of vehicle_tbl, which is located at the one-side of the relationship, moves into the table booking_tbl, which is located at the many-side. (This rule must always be satisfied otherwise you don't have a M:1 relationship)

2. The sole primary key of booking_tbl is booking_id. The other keys vehicle_reg and customer_id are foreign keys only. Therefore, you do not have a compound primary key.

3. just to satisfy completeness: If your pk booking_id were to move into vehicle_tbl you would have got an 1:M relationship what s obviously plain wrong.

Maybe you did incorrect implementation on MS Access. As I remember hardly, older versions of access demanded to put an index on the primary key fields, possibly also on foreign key fields, but I am not sure of that. Today versions of relational database system automatically put an index on primary key fields. If not, performance of large tables would be slow down dramatically.

As trudge already stated, access isn't that a good choice for learning relational database design. There are tremendously better RDBMS as for example: MS SQL Server Express, Oracle 10i Express, Mimer SQL, Firebird (aka Borland Interbase), Postgresql, Sybase SQL Anywhere (absolutely great!) or unrivaled Derby (aka JavaDB or Cloudscape) ... They all are free of charge and true relational databases as opposed to MySql.

brs,
cliff

ps: Neither your above mentioned schema nor the graphical result of MS Access can be called ERM. You may google for Entity Relationship Model by Peter Chen to get an impression.