I need to normalize data for a car rental shop. this is the uesr views data:

CARS: License plate number, type of car, required driving license class, price per day
DRIVERS: Name, contact info (phone no – both cell and land line, email, address), driving license class, price per day
ORDERS: Customer, rental days, type of car, name of driver (if required), car returned in good order?
COSTUMERS: Name, Contact info (both land line and cell phone number, email, address), driving license class and serial number
PAYMENT: Type of payment, amount paid, date


can some expert give me an idea of the design, i mean all that 1 2 3 normalization forms ?



thanks for the help

CARS: License plate number, type of car, required driving license class, price per day

To support the above table you will need the following additonal tables

Car Types
Driving License classes

PAYMENT: Type of payment, amount paid, date
To support this table you will need

Payment types table

This design is all over the place. :eek:

Orders would be the main table. It would have a one to many link to a customers table and a one to many link to a car_id table. I'm not sure what the payments table you have would contain? Generally when I've rented a card it had one payment.

And depending on the purpose for the Car Type and Driving License class tables I'm not sure if I'd link these or just use them for look up tables...

Sounds like a nice school project...

Sounds like a nice school project...

Sounded like that to me as well - :rolleyes:

Edit: 100:1 he never logs in again :)

Sounds like a nice school project...
yeah don ask me ask my brother.... the jerk is all the time on this stupid World of Warcaft ... gonna kick his ass.... anyway thanks for the help to all of you

You did log back in - ?! Sorry for the comments. Hope you get it working.

This design is all over the place. :eek:

Orders would be the main table. It would have a one to many link to a customers table and a one to many link to a car_id table. I'm not sure what the payments table you have would contain? Generally when I've rented a card it had one payment.

And depending on the purpose for the Car Type and Driving License class tables I'm not sure if I'd link these or just use them for look up tables...
well the payments table i would use the methods of payment (cash,credit card,etc)

So it would simply be a look-up table and not have an established relationship.

i am not sure.... i really don understand all these things..i am trying to learn it from some guides.... but it sucks :D

Well I would be willing to help, but I am NOT going to spel it out for you....

If you make your stuff in the 0 normal form and tell me what the first normal form is... we can go from there.

Well I would be willing to help, but I am NOT going to spel it out for you....

If you make your stuff in the 0 normal form and tell me what the first normal form is... we can go from there.
well man i don understand u with that 0 normal form but i know that the 1st one is about :
i must not duplicate data within the same row in a table ;
after that i must c8 separate tables for the related data and set primary keys.

0 normal form is, just a list of data you need without thinking about tables or normal forms whatever... Just a long list of data field you need.

The first normal form takes care of generating your basic table ideas. Split the list of data you have into seperate categories/tables and find a Primary Key of these tables.

well i have the 0 form then...all the data i need to fill in ms access....i don know how to split the data in correct tables...ffs

You do... and you are the only one that does.

Start by taking all the "car parts" and putting them into a Car table.
Then take all the "Customer parts" and put them into a Customer table
etc.

well i have the 0 form then... the whole data i need to fill in MS access... but i cant do all these tables correctly...ffs
this is the data in 0 form : http://uploading.com/files/M22VMTSH/Data to be filled in the DB.pdf.html

ffs??

Looks like you are about 90% of the way to normalization allready with that data...
Problems I see:
1) Nothing has a Primary key
2) what happens with people that have 3 cell phones or 2 landlines? 2 Email addresses?
3) Multiple licences...

well i have the 0 form then... the whole data i need to fill in MS access... but i cant do all these tables correctly...ffs
this is the data in 0 form : http://uploading.com/files/M22VMTSH/Data to be filled in the DB.pdf.html

Repeating your post, will lead to me repeating mine....

ffs??

Looks like you are about 90% of the way to normalization allready with that data...
Problems I see:
1) Nothing has a Primary key
2) what happens with people that have 3 cell phones or 2 landlines? 2 Email addresses?
3) Multiple licences...

its ok man don bother with me i will handle it somehow ;)
thanks anyway

Good luck, mr Know it all.

if u know so much u would help me like some other guy on other forum and not just posting stupid excuses... so thatswhy don even post ...

Sorry if I am not in the habit of doing your homework for you....

Why dont you just BUY your degree online??

lol hey dutch i asked for help and if u don wanna help me stfu... u think u know so much a ? i doubt that u have a degree at all...

my my. i just had a look at that pdf... the teacher ought to be shot for spelling "customers" as "costumers".... or maybe the teacher is suggesting all the customers are costume designers for tv and film?? ;P

daliboy, normalisation works like this:

find all the data you need. now, group anything that can only belong to a car and put it in a car table. this table can have many cars, but each car has its own record (what you may think of as a 'row' in the table).

now, a person has nothing to do with a car because a car may be owned by more than one person and can even change who it belongs to -so, a different table is used for people.

now, a landline and mobile etc are also sperate to people b/c these can change too, so contact details are a separate table also. this makes it easy to assign several different contact details for one person (e.g., they may have one mobile for personal use, one for work, a landline at home, a landline at work, an email and fax for work, no fax at home but has email... etc...)

it follows to say that someone's phone number has no place in the car table, and so on. this normalisation is easy once you understand that you keep together the things that will not change (e.g., a car will always be the same make and model, no matter how old it is or who owns it) and keeping separate things that can change (e.g., people and cars can change - i.e., in a car rental company, several people will rent one car over the course of a year but also several cars may be rented to one person over the course of a year.)

having said all that, your teacher has given you EXACTLY what to do and how they want you to structure your database tables in that pdf. the first three tables are the base tables, with the orders table being a junction table for all of them. i presume your teacher expects you to make forms and such for the database to make it user-friendly, which is the harder part. i dare say it would need some sort of reports as well.

and daliboy, don't worry about the other posts - if they upset you ignore them, don't take the bait, makes you look aggressive.

and daliboy, don't worry about the other posts - if they upset you ignore them, don't take the bait, makes you look aggressive.
Surely it was dliboy who upset them first and is looking on other forums as well for posters to do his homework?;)

Surely it was dliboy who upset them first and is looking on other forums as well for posters to do his homework?;)

touche ;)

as for homework... i did a tiny bit of access waaaaay back when i was uni, and i'm SURE they actually tought us how to do this stuff first before making us do an assigment on it...? either daliboy's teacher is crap, or daliboy skipped a couple of classes!

either daliboy's teacher is crap, or daliboy skipped a couple of classes!
or daliboy is just lazy and wants others to do his work for him, in any case slinging insults at respected members of the forum is more likely to get his questions ignored. Let's face it he's too lazy to even attempt to respond in English;)

lol hey dutch i asked for help and if u don wanna help me stfu... u think u know so much a ? i doubt that u have a degree at all...

Bachelor in Information Technology, thank you very much and earned it the HARD way...

If you want me to help you... I will, but you have to do that hard work to earn the help... like RSVP and IIRC b4 asking anything RTFM.

I suggest you start googling yesterday to find a theses you can copy to hand in...

my my. i just had a look at that pdf... the teacher ought to be shot for spelling "customers" as "costumers".... or maybe the teacher is suggesting all the customers are costume designers for tv and film?? ;P

daliboy, normalisation works like this:

find all the data you need. now, group anything that can only belong to a car and put it in a car table. this table can have many cars, but each car has its own record (what you may think of as a 'row' in the table).

now, a person has nothing to do with a car because a car may be owned by more than one person and can even change who it belongs to -so, a different table is used for people.

now, a landline and mobile etc are also sperate to people b/c these can change too, so contact details are a separate table also. this makes it easy to assign several different contact details for one person (e.g., they may have one mobile for personal use, one for work, a landline at home, a landline at work, an email and fax for work, no fax at home but has email... etc...)

it follows to say that someone's phone number has no place in the car table, and so on. this normalisation is easy once you understand that you keep together the things that will not change (e.g., a car will always be the same make and model, no matter how old it is or who owns it) and keeping separate things that can change (e.g., people and cars can change - i.e., in a car rental company, several people will rent one car over the course of a year but also several cars may be rented to one person over the course of a year.)

having said all that, your teacher has given you EXACTLY what to do and how they want you to structure your database tables in that pdf. the first three tables are the base tables, with the orders table being a junction table for all of them. i presume your teacher expects you to make forms and such for the database to make it user-friendly, which is the harder part. i dare say it would need some sort of reports as well.

and daliboy, don't worry about the other posts - if they upset you ignore them, don't take the bait, makes you look aggressive.
i am glad that there are people like you in the forum...
thank you very much ...

touche ;)

as for homework... i did a tiny bit of access waaaaay back when i was uni, and i'm SURE they actually tought us how to do this stuff first before making us do an assigment on it...? either daliboy's teacher is crap, or daliboy skipped a couple of classes!
well the teacher is a Czech women who is a very good mathematician but this is her first class for databases... so i can say she sucks in databases.... i ask her some question and she replies like : well try to find some help on the internet .... lol and i am like WTF !! i mean she is just repeating us to follow these 3 normal forms .... is that a good explanation ? i guess NOT

So Wiklendt come and be a teacher in the college :D

Perhaps it is not her teaching but rather your understanding of it... She (and/or your books) must have covered normalization and other basics.

If you dont understand the basics... its back to the drawing board, which is where I tried to get you.... instead... you just want the easy answers...

well the teacher is a Czech women who is a very good mathematician but this is her first class for databases... so i can say she sucks in databases.... i ask her some question and she replies like : well try to find some help on the internet .... lol and i am like WTF !! i mean she is just repeating us to follow these 3 normal forms .... is that a good explanation ? i guess NOT

So Wiklendt come and be a teacher in the college :DIf you are having difficulty in understanding what your teacher is saying about normalization then you will find it very helpful to use google to find helpful articles.

As a starter try reading these two articles.

http://www.datamodel.org/NormalizationRules.html

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Of course you may have left it a little late but you really do need to understand it if you are ever going to be a competent databse designer,

And... A forum setting is a poor tool, from the nature of the technology, to ask much more than simple questions as things tend to drag out as questions and responses get misconstrued. Not to mention those that are on the helping side really don't want to get tied up in a dialog that stretches for pages...imho :)

Perhaps it is not her teaching but rather your understanding of it... She (and/or your books) must have covered normalization and other basics.

If you dont understand the basics... its back to the drawing board, which is where I tried to get you.... instead... you just want the easy answers...
man the teacher can barely speak english... thats her main problem... and she is employed in a College where lectures are lectured in ENGLISH....

so i have a design like this till now:
CARS : license_plate_num (PK), car_type, req_driving_license, price_per_day
DRIVERS : driver_id (PK), driving_license_class, price_per_day, id_number (FK)
CON.DETAILS : id_number (PK), cell_phone_private, cell_phone_work, landline_phone_home, landline_phone_work, email, address, name, surname
CUSTOMERS : serial_num (PK), id_number (FK), driving_license_class
ORDERS : invoice_number (PK), serial_num (FK), driver_id (FK), car_rented, from, to, car_returned_ok?
PAYMENTS : invoice_payment (PK), payment_type, amount_paid, date
LICENSES : license_type (PK), description
PAYMENT_TYPES : payment_type (PK), description

i don know how to make all these conjunction tables.... can u pls help me ? and tell me if i have some problems till now with the normalisation

(con)junction tables are tables that resolve a N:M relationship.
They contain the primary key of both the N and M tables along with any data that is required for this relationship or dependant upon this relationship.

Proof that you do know how to do it... just beeing lazy in asking for the solution...
Your ORDERS table is such a Junction table resolving the N:M relationship that DRIVERS/CUSTOMERS have with CARS

General comments:
What a person (Connection details) dont need Postcode/City
You have lookups for Licenses, but what is a car_type then??
Your car business will only rent cars by the day? Not day "part", morning/afternoon/evening? Not by the hour?

Tips:
Dont use special characters including _)(*&^?>< etc in any table/Column names
Use a naming confention, all tables should start tbl, queries qry etc.

Perhaps it is not her teaching but rather your understanding of it... She (and/or your books) must have covered normalization and other basics.


....hm, sort of. although at uni we did a little db stuff, i don't actually recall being taught normalisation - that i really only learnt this year (the hard way! LOL) when i started my equine massage db.

but i agree it's fundamental in understanding how databases should be put together / how they should work. (thanks Rabbie for those links - i never knew there were an actual set of rules!)

So Wiklendt come and be a teacher in the college :D

funny you should say that, i'm actually going to be looking into doing a diploma of education here in australia in the next year or two to become a teacher, though my goal will be to teach high school science/biology (which is what i've been trained/working in)... people have often said i should be a teacher.

so i have a design like this till now:

CARS : license_plate_num (PK), car_type, req_driving_license, price_per_day
DRIVERS : driver_id (PK), driving_license_class, price_per_day, id_number (FK)
CON.DETAILS : id_number (PK), cell_phone_private, cell_phone_work, landline_phone_home, landline_phone_work, email, address, name, surname
CUSTOMERS : serial_num (PK), id_number (FK), driving_license_class
ORDERS : invoice_number (PK), serial_num (FK), driver_id (FK), car_rented, from, to, car_returned_ok?
PAYMENTS : invoice_payment (PK), payment_type, amount_paid, date
LICENSES : license_type (PK), description
PAYMENT_TYPES : payment_type (PK), description



the way i organise my contacts (and you can apply this concept to any data series) is more like this:
tblCustomers : CustomerID (PK), Salutation, NameFirst, NameLast, DOB, Notes

tblCustomers_Contact : ContactID (PK), CustomerID (FK), ContactTypeID (FK), Cell, Landline, Fax, Email, Street, Suburb, PostCode, State, Country

tblCustomers_Contact_Type : ContactTypeID (PK) (e.g., "private", "work")
this is how the above works: many people are put into the customers table:
person one has no job but they can be reached on their home landline, their personal mobile, or by email. so in the customers_contact table i select the person and the type of contact i'm adding - in this case it will be "private" (via the ContactTypeID (FK)).

person two has a job, and they prefer to be called there. so i put in the person details, then in the customers_contact table i put in a record and call it "work" (via the ContactTypeID (FK)).

person three has a job, but they work nights, however it turns out i may need to contact this person at any time of day, so i need both his home and work details, so in this case i put in two records for this one person in the customers_contact table, one for "private" and another "work", and then use the available fields to fill out the multiple cell, landline and email details.

now, if the home and work fields were both in one table, you are wasting lots of bytes storing empty fields for people who don't need them. you only need one field each for e.g., cell, landline and email. the ContactTypeID is what will distinguish between them.

the other thing i'll say is about how i've named my tables. against the warnings of namliam (any many others on this topic! sorry, i just like the tidyness it offers), i actually do use the underscore (_) character in my tables to denote that it's a 'sub' table of another (i also do this with forms and reports, as it is common to need subForms or subReports).
e.g., the table tblCustomers_Contact_Type is a table from which tblCustomers_Contact will get more information (in other words, tblCustomers_Contact_Type provides the list of items ("private","work") via a drop-down (or combobox) to the table tblCustomers_Contact). if in the future you decide you need MORE options for contact type, it is easy to simply add new contact type and then have customers assigned this extra contact type

a lot easier than having to put in a new set of contact fields into one table (i.e., cell_phone_private, cell_phone_work, cell_phone_work_2, cell_phone_oveseas is better constructed by having one table "private", "work", "overseas" that you link to the contacts table and then fill out fields that are already there for the Cell.)...

one more reason i name my assets like this is because then they are automatically 'grouped' in the assets box (in A2007 it's the "navigation pane", i believe) b/c access orders the tables etc alphabetically, so any tables that are FK to the customer table, for example, are listed in access together. i won't have to remember that "tblType" is a subtable of "tblContact" b/c if they're named "tblContact" and "tblContact_type" it will be without a doubt and clear as day and obvious that these two tables somehow belong to each other.

a good way to know if you are putting in redundant fields is this: if you are naming your fields: (name, date, something_1, something_2), then that is a sure sign that there probably should be a separate table called tblSomething, with the different Something options in that table. it allows for easy expansion when you require it, but also streamlines the db such that precious data storage is left available for those important records that need to be saved.

to the gurus: please let me know if this is a faux pas! seems to work for me pretty well, though.

the other thing i'll say is about how i've named my tables. against the warnings of namliam (any many others on this topic! sorry, i just like the tidyness it offers), i actually do use the underscore (_) character in my tables to denote that it's a 'sub' table of another (i also do this with forms and reports, as it is common to need subForms or subReports).
e.g., the table tblCustomers_Contact_Type is a table from which tblCustomers_Contact will get more information (in other words, tblCustomers_Contact_Type provides the list of items ("private","work") via a drop-down (or combobox) to the table tblCustomers_Contact). if in the future you decide you need MORE options for contact type, it is easy to simply add new contact type and then have customers assigned this extra contact type

a lot easier than having to put in a new set of contact fields into one table (i.e., cell_phone_private, cell_phone_work, cell_phone_work_2, cell_phone_oveseas is better constructed by having one table "private", "work", "overseas" that you link to the contacts table and then fill out fields that are already there for the Cell.)...

one more reason i name my assets like this is because then they are automatically 'grouped' in the assets box (in A2007 it's the "navigation pane", i believe) b/c access orders the tables etc alphabetically, so any tables that are FK to the customer table, for example, are listed in access together. i won't have to remember that "tblType" is a subtable of "tblContact" b/c if they're named "tblContact" and "tblContact_type" it will be without a doubt and clear as day and obvious that these two tables somehow belong to each other.
With regard to table/field naming I feel it is a matter of personal taste if you use underscores or not. I think it is important not to use any other "Special" characters in names as these can cause problems later on.
a good way to know if you are putting in redundant fields is this: if you are naming your fields: (name, date, something_1, something_2), then that is a sure sign that there probably should be a separate table called tblSomething, with the different Something options in that table. it allows for easy expansion when you require it, but also streamlines the db such that precious data storage is left available for those important records that need to be saved.

to the gurus: please let me know if this is a faux pas! seems to work for me pretty well, though.I always regard the something_1, something_2 situation as a warning that there are normalisation issues that will need investigating.

With regard to table/field naming I feel it is a matter of personal taste if you use underscores or not. I think it is important not to use any other "Special" characters in names as these can cause problems later on.

If you can apply the _ correctly and consistantly it is not a problem however this is immediatly the problem.

I right now am working with a Million $ app that uses _
ID_Cust is consistantly thru the database
Cust_Name ... OK
CustStreetName ... Erm??
CustPostal_code ... What?
Also table names:
Customer
Cust_Details
Customer__Parent <= notice YES double _ and the Cust vs Customer as subtables.

The adding or missing _ is really driving me crazy... if implemented complete and full... I dont see the issue, but often times it is not causing headaches

Consistancy and a proper naming convention is really really important for future maintenance. In a "drag and drop" kind of environment like access maybe a less of a 'real' issue... but once get used to things like this... its hard to get out of the habit.

If you can apply the _ correctly and consistantly it is not a problem however this is immediatly the problem.

I right now am working with a Million $ app that uses _
ID_Cust is consistantly thru the database
Cust_Name ... OK
CustStreetName ... Erm??
CustPostal_code ... What?
Also table names:
Customer
Cust_Details
Customer__Parent <= notice YES double _ and the Cust vs Customer as subtables.

The adding or missing _ is really driving me crazy... if implemented complete and full... I dont see the issue, but often times it is not causing headaches

Consistancy and a proper naming convention is really really important for future maintenance. In a "drag and drop" kind of environment like access maybe a less of a 'real' issue... but once get used to things like this... its hard to get out of the habit.
Any naming convention if not applied consistently can cause problems. My point was that IMHO it is Ok to use _ in names if you want to. As I said it is a matter of personal preference. Cust v Customer can be a problem regardless of the use of underscores

Ppersonally I don't use the underscore method because it is just extra typing but if it suits some people then that's OK by me.

Ppersonally I don't use the underscore method because it is just extra typing but if it suits some people then that's OK by me.
Fine by me too... But given Million$ apps cannot get this right... it is safe to assume we 'normal' people will F-up as well.

Better to not use it at all, as a rule of thumb.

I always regard the something_1, something_2 situation as a warning that there are normalisation issues that will need investigating.


ah, yes, that's a better way of putting it. it's what i was trying to get at... the solution of using a new tblSomething in my example above should normalise that data and rectify that issue.

thanks for putting it in proper terms rabbie (i.e., "normalisation" rather than my huge rant!) :D