data normalization pls help ! (1 Viewer)

[daliboy]

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 ...

 

[namliam]

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

Why dont you just BUY your degree online??

 

[daliboy]

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...

 

[wiklendt]

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.

 

[Rich]

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?

 

[wiklendt]

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!

 

[Rich]

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

 

[namliam]

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...

 

[daliboy]

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 ...

 

[daliboy]

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

 

[namliam]

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...

 

[Rabbie]

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

If 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,

 

[KenHigg]

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

 

[daliboy]

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....

 

[daliboy]

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

 

[namliam]

(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.

 

[wiklendt]

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

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.

 

[wiklendt]

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.

 

[Rabbie]

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.

 

[namliam]

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.