PHP Tutorial–Lesson 4: Introduction to PHP- Part III

•Today, I’ll put you in a project, and try to analyze it step by step  

•Suppose, a customer has a company, and asks you for a method for storing his employee information, and accessing it when needed.

•You should put in mind, that your customer in best case has minimum knowledge about databases, and all what he need is what, not how.

•Its your turn to ask the customer, about nature of data he wants to collect.

•Let’s say the customer told you he wants to store employee name, social number, department, job title, salary, cv, telephone, address.

•These little words from your customer, helps you imagine how your table structure will look.

Analysis Step:

•Analysis step simply means determining proper data types for data you are going to collect.

•Let’s start:

•Employee name: name its proper data type is string, it is possible to make its type text or longtext, but we want to design quick, suitable, and with the less cost that meets requirements for our table.

•Social number: social number consists of number of bits, so int is suitable, we might thought about double here, but remember that double and float are used when your number consists of decimal numbers. So best choice here is int.

•Department: might be int or string! Because, some companies label their departments by numbers for example: department 1, department 2… etc. and some companies might label their departments by name, for example: Computer department, Human Resource department… etc.

•Job title: string, possible values: administrator, manager, head-master, consultant… etc.

•Salary: double.

•CV: text, we might thought about string, but in fact, string is not enough, because cv’s sometimes are big, and might contain from more than one page, so string does not meet your customer requirement, longtext is true, but is too big. We might not use all the storage offered by longtext.

•Telephone: int, but I prefer string, because you might separate post-code from number by dash - .

•Address: string.

•What else?

•Your customer told you all information he want to collect about his employees. But there still another thing, will help you in your application, and facilitate your job, which is, give each employee an id.

•This id must has a properties of uniqueness, numerical and increments.

•Why?

•Uniqueness: this id should be unique, to differ each employee from another. if you work in a company that has more than 100 thousands of employees or customers, you will notice that there might be high similarity in some names. so you can’t differ one from another. But when you deal with numbers no way to find two numbers identical.

•To make id unique, its type should be int and has a property of incrementing.

Design Step:

Once we had analyzed our customer inquiry. Then it is the time to design our table, or in other words, create our table.

CREATE TABLE employee

(

id int  AUTO_INCREMENT,

PRIMARY KEY(id),

name varchar(150) NOT NULL,

socialNo int NOT NULL,

department varchar(100) NOT NULL,

jobTitle varchar(50) NOT NULL,’

salary double NOT NULL,

cv text,

telephone varchar(25) NOT NULL,

address varchar(200)

)

Notes:

•CREATE TABLE: a query known in MySql db, its function is creating a table, in our example it creates a table called employee.

•Table attributes (columns) enclosed within parenthesis () and each attribute except last is followed by a comma.

•NOT NULL: means that this info must be filled with suitable data. We use it where an attribute must has a value. If it does not matter whether this field has value or not, we don’t write NOT NULL.

•AUTO_INCREMENT: means that this is a counter and increments on every insertion to the table by one.

•PRIMARY KEY: means this field value should not be repeated in any row. ( unique)

•Each table has only one primary key. And this field cannot be null.

•varchar: tells the db that this field value is character, and maximum size of character is determined by integer number. varchar(25) means this field is a string that at most its length is 25 character.

•Cv, and address fields might be null.

Previous Query will generate an empty table looks like this one.

To download this lesson click here: PHP Tutorial–Lesson 4: Introduction to PHP- Part III

Best Wishes,

Anas Jaghoub