nonus2012;11007505 wrote:
1- my manger ask me to put emp_id primary key not auto increment he need me to let it as start date of employee+3 random number example 120406111 (start date of that employee 06/04/2012+111 is generated randomly for each employee primary key) i don't know if that good idea or not i afraid it affect in database i really done it but i don't know if it good or not
It's not a good idea. A primary key is there for one single reason, to uniquely identify a relation (or row or tuple whatever you prefer to call them). A primary key may consist of one or more item of data in a relation as long as it's guaranteed to be unique for each relation. Each such set of one or more data items to uniquely identify the relation is called a candiate key (CK), and the chosen one is called the primary key (PK). For a person this might be a Social Security Number. It might also work using a compound key of (first name, last name, street address, zip code), which of course assumes that no two people with the same name live together.
However, if you do not have a CK, you can of course create one, or even if you have one or more CKs you can create one, usually using an auto generated number. For example, since the PK will be stored several times (in indices and as a foreign key in related tables) you want the size of it to be relatively small, not just directly for storage purposes, but mainly since less storage means more keys are retrieved when reading one disk block of indices, it speeds up the time to find a specific relation using the key when the key is smaller.
The primary key has no other purpose and should have no other purpose than identifying a relation. If you want to keep the employment start date of an employee, store the start date separately in a date field. This will allow you to actually use the date as a date, calcualte the length of employement, sort by it etc. Moreover, [DATE]+111 makes no sense at all since it looks like a math experssion but isn't one and adding things to a date makes no sense. [DATE] concatenated with 111 doesn't make sense either, since the first is a date, while the string [DATE]111 (with or without the +) is garbage.
As for size, the string he suggests as PK contain 14 characters which is likely to use up 14 bytes. A four byte unsigned integer lets you keep 4.2 billion employees (two thirds of the world's population) and an eight byte unsigned integer gives you 18 billion billion (or roughly 3 billion times the world's population), so I'd say the 4 bytes should suffice, and most definitely the eight.
Moreover, when it cames to schema design (the table structure of your database) of relational databases, you should read up on Database Normalization (search wikipedia for an intro) and the Normal Forms (NF) and stick to those! Higher NF is better NF, so 1NF won't cut it. You should keep it in 4NF. You may however most likely disregard 5th and 6th normal forms.
Anyway, off the top of my head, even 1NF forbids the use of compound values - which [date] + [other stuff] most certainly is. I do remember reading that this criteria was later removed due to the (in my opinion very academic) question of what constitutes a compound value. Without delving into this (read up yourself) I'd at least say that if two things such as [date] and [random number] which aren't even closely related, then they clearly are a compound value and forbidden.
That said, I recommend an INT UNSIGNED using an auto generated number (varies slightly how they are implemented in various DBMSs) which leaves you free from worry on how to generate them, guarantee their uniqueness and such! Should he still insist it's important to know which employee was hired first within a specific day (seriously?) then store the this as a full DATETIME called employment_start instead! This also has other upsdies. Let's say you hire 3 employees on one day: Adam at 10, Bob at 11 and Cesar at 12. the two ways of storing it gives
name emp_start ordinal
adam 2010-01-01 1
bob 2010-01-01 2
caesar 2010-01-01 3
name emp_start
adam 2010-01-01 10:00:00
bob 2010-01-01 11:00:00
caesar 2010-01-01 12:00:00
After firing bob (and deleting him from the db)
SELECT MAX(ordinal) FROM emp
WHERE emp_start = '2010-01-01'
would give you 3, while his now the second person hired that day which still works here. Sure, you might keep bob in the table and update status = 'fired', but the point here is that you do not need the ordinal at all, it is called a "derived" value.
SELECT COUNT(*) AS ordinal FROM emp
WHERE DATE_FORMAT(%Y-%m-%d, emp_start) = '2010-01-01'
ORDER BY emp_start ASC
will give you this derived value.
And well... storing random gibberish in the db for no good reason? I can manage to simulate that without even retrieving data... I might not simulate the same data ever time I retrieve an employee, but seriously: what reason is there for a random number in the first place? Provided a VERY good reason for this random number, certainly: store that number in the relation. But do not do it as part of anything else.
You might of course go with a compound PK of (date, random_number), but now you'd once again need to keep track of what random numbers has allready been assigned on a given date.
nonus2012;11007505 wrote:
2-manger ask me to put report_to in same table as i told u before but i don't know how i can differ manger from employee he told me to put all employee as employee but each one has report to another employee so i don't know what if there is top manger and not report to any one so what i must put in report to?
Once again: read up on database normalization.
As I recall it, there are people who are against allowing null values in relations and claim that such values should be kept in a separate table, but for simplicity I don't really see the point to this off the top of my head. Thus, you might stick to having report_to as a field in the employee table and either leave null (for the top manager) or put the id of another employee there for everyone else. If you go with a separate table, you'd have no rows in it for a top manager and one row for anyone else.
I usually stay away from that extra table unless you actually need to allow for several related relations such as one employee reporting to two different people since he works both in accounting and support and has two bosses, which would result in two rows in the reports_to table.
Anyway, with the one-to-one relation (between the tuples) in this case, if you side with the people who are against null values you could of course do the same as I first suggested but put the mangers own id in the reports_to field since you can claim he reports to himself. Wether you go with this or the null value approach, you'd simply have a foreign key contraint referencing the same table: FOREIGN KEY (reports_to) REFERENCES employee(id)
nonus2012;11007505 wrote:
3-i am doing administrator pages (like add, edit, update)add as which is add all employee data include his report_to manger,his type , his job , his organization and his personal data as name which is divided to two field(first name , last name) so when i add new employee i will assign to him manger so how can i do it and by which name i show it must be full name (first name+last name)
You assign manager by his employee id, since this is the primary key of the employee table, is guaranteed to be unique and moreover was chosen to be the primary key. However, to the user assigning mangers, you will display the name rather than the id (or in case of multiple mangers with same name, both name and id so they know who's who).
This can be done with a select box holding ids as values and first + last name as text, that is
<option value="[id goes here]">Name goes here</option>
and data retrieval is straight forward as well
-- in case of reports_to using the managers own employee id
SELECT CONCAT_WS(' ', first_name, last_name) AS name, id FROM employee WHERE reports_to = id
-- in case of reports_to using leaving the reports_to null for managers
SELECT CONCAT_WS(' ', first_name, last_name) AS name, id FROM employee WHERE reports_to IS NULL
or possibly list of manager employee ids selected some other way. You get the point.
nonus2012;11007505 wrote:
4-in administrator edit page i have problem that when i need to update data as employee organization so i need to update it by choosing another organization so i retrieve data in text box and beside it drop down list to choose new organization(which is retrieved from database)so i need when he choose organization from dynamic drop down list so it must automatic change text box old value by new organization chosen value so how to do it ?
No idea what you mean by organization. But let's say this organization is stored in a table called "organization", then it will have an id of some kind (once against most likely an auto generated id as discussed in the first topic. This ID will be the only thing stored in employee.organization, or in the case of one employee having many such relations to organizations, they will be stored in a link table maybe called employee_orgnizations as (employee id, organiztion id). Either way, if you go with option one, replace the org_id with the new org_id for that employee. If you go with the second remove the first relation, and insert the new one.
