Relational Database PART I

Tutorials on PHP, databases and other aspects of web development. Before posting a question, check in here to see whether there's a tutorial that covers your problem.

Moderator: General Moderators

Post Reply
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Relational Database PART I

Post by Draco_03 »

Hi, I've come to think that I should maybe give more information on relational databases.

A bit of history on it.

Age : over 25 years old (born in 1969)
Taste : Dislike slow computers (25 years ago, computer were REAL slow). Part of why it wasn't as use as the other type of relation (at that time). When the microprocessor was invented, The age of Relational Database was truly upon us.
Name : Derived from the word relation which is part of a set theory. It is not because the table in the database can be related one to another in the database.
Father : Dr. Edgar F. Codd



RELATIONS

A Relational database store data in relations (we perceive it as tables).

We can categorize the relations in 3 different types


One-to-one relationship

the less common type of relationship.
A pair of table bears a one-to-one relationship when a single record in the first table can be related to only one record in the second table, and a single record in the second table can be related to only one record in the first table.

view the image here

Okay, now go back to the image, you should be able to tell something here...
well if you didn't find it I'll tell you

The designer of the database could have put all the info in one table.
So why he didn't?
Theses questions are what makes the difference between a good designer and a very good one.
The very good one though about the facts that not everyone should have access to the compensation a employee makes. Only authorized personnel can view the compensation table, and everyone can view the employees one.(we still could have put everything in one table put just harder to implement).




One-to-many relationship

Let's now talk about the most common type of relationship.
One-to-many relationship exist between a pair of table when a single record in the first table can be related to one or more record in the second table, and a record in the second table can be related to only one record in the first table.

In this example, you have :
the employee of a mega corporation that want to "rulez" over the world. Let's call it Microsoft.
These employee can have each one of them one or more clients attached to them. But, a client can have only one employee that takes care of him.
So you get a one-to-many relationship.

This particular relationship is not the most common for nothing.
-help eliminate duplicate data
-keep redundant data to a minimum



Many-to-Many relationship

Okay, this one is the 2nd most common type of relationship, but a bit more complicated to illustrate.(don't worry, I'll find some good example somewhere 8))

A pair of table bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.

Guess what, I did find some good example.

Well here's a typical example, when you go to school you have more than one classes to attend to right (hopefully)
And a class can have more then one student attending.
yep yep yep...


Self-referencing relationship

This type of relation is NOT between a pair of tables, it is instead a relationship between the records within a table.
I'll go quickly over this one.(no pictures, only text example)


One-to-one

If you have different members within a organisation, and a given member can sponsor only one other member within the same organisation.

You would have a table with the membersid, name lname sponsor id.
In the sponsorid field you would have the memberid of whom you got sponsored.



One-to-Many

A customers of a given organisation can be referred by (other ppl, you know..other customers.). And one given customer can refer one or more customers.

I have a web host, I can refer people to this host (and get some kind of discount). I can refer more then one customer, so if I refer Feyd and PatrickG to this host, in the table "customers" under they're name in the field referred by there would be my customerID.



many-to-many

Okay god this one is complicated to explain.
Let's give it a shot.

You have different product, each of them are made out of smaller part .
So a particular part can comprise different components part AND be itself component of another part.

Let's say a chain have little bolts on it

part -> chain
components -> bolts

and a bicycle has a chain to be functional

part -> bicycle
components -> chain

So a chain is both a part and a component.
You can by a chain, you can by a bolt and you also can by a bycicle, they are all different part/components of the same store.

Well
That's about the relation in a relational database.

Ps : any question pm me, and give me your feedback, good or bad 8)

enjoy

EDIT : spelling
Last edited by Draco_03 on Wed Aug 18, 2004 9:36 am, edited 2 times in total.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Thanks for writing the tutorial - could you run this tutorial through a spell-checker, please? :)

Cheers

patrikG
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

Ya the spelling :oops:
well it's done now :)

thx 8)
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

I think patrik just sits back and waits for you to post draco so he can ridicule your spelling mistakes

lol

:wink:
Post Reply