Normalization question

[brad62]

This is something that I find pretty hard to do the easy way, since I'm a newbie.
I will try to describe what I'm looking for and since my own thoughts are not
that easy to follow I thought I will make a fictive Car Rental instead. So you
guys will understand what it's all about.

Let's say I have a small business where people can rent a car.
I have 5 employees in a table called USERS (user_id, username, password, phone)
I have a table with CARS (car_id, model, booked, registered_by)
And a third table for BOOKINGS (book_id, registered_by, booked_car, customer)

So my site will start logging in a user (where I also start a session for that username)
Then there will be a list of all the bookings in the MySQL
Here I want to see booked_car, model, registered_by and also the cars that are available.

If a car isn't in the list (which is true the first times of course), the user will be
able to click a button to add a car to the database.

Now I started to be unsure...
When a user adds a car it won't show up in the table, since it's not booked.
Should I make an insert into the BOOKINGS at the same time as the car is added,
that says it's linked to CARS but mark it as unbooked.

Or should I just add the car and in the table showing all the bookings make something
that also shows cars that are not booked?

I just don't understand how to make the connections here. And which way is the right
way to do it here. Or the best. And smartest?

I guess it's best to have some kind of connection between all the 3 tables with an
id telling every table how they connect.
Like users.user_id = bookings.registered_by
and users.user_id = registered_by
and cars.car_id = bookings.booked_car

Or is that too many? It's so hard to plan all the parts the first time.
Since a car can be booked several times as well. I might want to keep old bookings
alive as well. For future use. Who knows? They say one should plan a long time ahead.

I hope you people understand this complicated description of my problem.
But I think a car booking would be pretty easy to use as an example.

[r937]

If a car isn't in the list (which is true the first times of course), the user will be able to click a button to add a car to the database.

the car isn't in the car table if the company buys a new car to add to its fleet

is that what you mean by "the user will be able to click a button to add a car to the database"?

When a user adds a car it won't show up in the table, since it's not booked. Should I make an insert into the BOOKINGS at the same time as the car is added, that says it's linked to CARS but mark it as unbooked.

no, don't do that

the booking table should only be for cars that are booked

if you add a new car to the fleet, you should go through two steps -- add the car, and then book it the same way as you book any existing car

i would never create an entry in a booking table that says it isn't really booked

Or should I just add the car and in the table showing all the bookings make something that also shows cars that are not booked?

no, don't do that

the cars that aren't booked should not be in the booking table at all

if a customer wants to book a car, you just search for all the cars that aren't in the booking table

users.user_id = bookings.registered_by
and users.user_id = registered_by
and cars.car_id = bookings.booked_car

yes, that's fine

[brad62]

Well, I want all the cars to show up in that first list. Even if they aren't booked.

I wanna make a table showing all the cars, and a field showing if it's booked or not.

So, after logging in I want the user to see a list of all the added cars and also if they are booked or available.

But can't figure out how to solve it in the script...

SELECT WHERE bla bla bla....

;-)

[r937]

okay, now we're getting someplace

select c.carid
     , c.carserial
     , c.carmodel
     , case when count(b.id) = 0
            then 'Available'
            else 'Booked' 
        end as status
  from cars as c
left outer
  join bookings as b
    on c.carid
     = b.carid
group
    by c.carid
     , c.carserial
     , c.carmodel

[brad62]

So, what is "left outer" doing? Didn't get that part.
And, since the cars was only an example. I really need more options than only available or booked,
but that is probably it's easy to fix. I guess I could do something like
case when count(b.id) = 0 then available
when count(b.id) = 1 then booked
when count(b.id) = 2 then prel booked
a.s.o.

[r937]

So, what is "left outer" doing? Didn't get that part.

all rows of the left table (first one), with or without matching rows of the right table (second one) if any

so if a car has bookings, there will be a result row of carcolumns/bookingcolumns for every booking

and if a car has no bookings, there will be only one result row, with carcolumns/nulls

[brad62]

You give me great answers, but after looking through this again... I wonder...

join bookings as b

Why as b? Or is it just a short version of writing bookings?
I guess it is, since you did that earlier... I guess I'm pretty stupid here, but I just couldn't figure out that part...

[lilleman]

Hi,

Why as b? Or is it just a short version of writing bookings?

b is an alias for bookings. You can do the same thing with columns.

SELECT field AS myAliasForField FROM table

Yours, Erik.

[brad62]

I tried that on my site now, but it didn't work :-(
I use DW MX 2004.
Maybe it's a simple way to code it in DW?
It looks like the code wasn't handled the way it was supposed to, I guess.

I just want a table showing car, modell, booked by and status

Anyone?

[r937]

you tried what? and how didn't it work? did it crash the server? give a syntax error? return the wrong results? return no results?

start by dumping your table structure -- use the export option in phpmyadmin of whatever front end tool you're using