database design

khesraw · #1 (**permalink**) 07-25-10, 00:56

salam guyz!

my database project has three tables which are related in a child under child manner, i mean it has:

Person_Table--->(........it has its own columns.....)
Employee_Table--->(.....it has it is own columns.....)
Technician _Table--->(....it has it is own columns....)

now as u know every technician is an employee and every employee is a person, ok now i am confused with registering a technician that where should i register it(which table) and how to make the relationship between them (i know but not with details), so every time i query (and how) there should be not problem at all.

if you can help me please do it
thanks.

khesraw · #2 (**permalink**) 07-25-10, 01:50

salam guyz!

my database project has three tables which are related in a child under child manner, i mean it has:

Person_Table--->(........it has its own columns.....)
Employee_Table--->(.....it has it is own columns.....)
Technician _Table--->(....it has it is own columns....)

now as u know every technician is an employee and every employee is a person, ok now i am confused with registering a technician that where should i register it(which table) and how to make the relationship between them (i know but not with details), so every time i query (and how) there should be not problem at all.

if you can help me please do it
thanks.

r937 · #3 (**permalink**) 07-25-10, 04:19

Quote:

Originally Posted by khesraw

ok now i am confused with registering a technician that where should i register it(which table)

that's easy, you would register a technician in all three tables

Wim · #4 (**permalink**) 07-25-10, 04:27

To be able to add a Technician, you will first have to add him/her as an Employee.
To be able to add an Employee, you first have to add him/her as a Person.
Like:

Code:

CREATE TABLE Person_Table(
	PersonId	BIGINT	NOT NULL,
	....
	CONSTRAINT PKPerson_Table PRIMARY KEY (PersonId)
)

CREATE TABLE Employee_Table(
	PersonId	BIGINT			NOT NULL,
	BossName	varchar(max)	not null,
	....
	CONSTRAINT PKEmployee_Table PRIMARY KEY (PersonId),
	CONSTRAINT FK_Employee_Person FOREIGN KEY(PersonId)
		REFERENCES Person_Table(PersonId)
)

CREATE TABLE Technician_Table(
	PersonId	BIGINT	NOT NULL,
	Speciality	varchar(50),
	....
	CONSTRAINT PKTechnician_Table PRIMARY KEY (PersonId),
	CONSTRAINT FK_Technician_Employee FOREIGN KEY(PersonId)
		REFERENCES Employee_Table(PersonId)
)

INSERT INTO Person_Table(PersonId, Name, ...)
VALUES(1001, 'Brian', ...)

INSERT INTO Employee_Table(PersonId, BossName, ...)
VALUES(1001, 'Fred', ...)

INSERT INTO Technician_Table(PersonId, Speciality, ...)
VALUES(1001, 'Microelectronics', ...)

khesraw · #5 (**permalink**) 07-25-10, 05:31

thank you very very much from your perfect response.
Ok as Employee_Table is an example of Person_Table and Technician_Table is an example of Employee_Table, now i want to add two more examples to those one an example for the Person_Table by the name of Patients_Table and second an example for the Employee_Table by the name of Nurse_Table. please add my this question's answer to your above response.

if you will tell me i will be tankful
THANKS

Wim · #6 (**permalink**) 07-25-10, 15:52

Quote:

Ok as Employee_Table is an example of Person_Table and Technician_Table is an example of Employee_Table,...

"Brian" is an example of a Person and an Employee and a Technician.

Stop thinking in "is an example of" terms for the relationship between tables, think in terms of "is a" or "is a special case of" or "is a subdivision of", like in "an Employee is a Person and a Technician is a subdivision of the Employees". Like they thought you in Object Oriented classes about inheritance.

Also notice that I wrote "Employee" and not "Employee_Table". Employee is the name of a table, there is no need to repeat that in the name. Unless you want to be consistent and give all the columns in the table names like 'PersonId_Column", "Name_Column", ...

Regarding your question: show us the code you can come up with yourself. We'll be happy to correct you if that would be necessary. Doing the effort of thinking about the problem and trying to come up with a solution is a far better road to grokking things then having the answers spelled out for you. Just apply the same logic as in my first reply. Hint: is a Nurse a Person? an Employee? a Technician? How about a Patient?

r937 · #7 (**permalink**) 07-25-10, 18:23

Quote:

Originally Posted by Wim

Unless you want to be consistent and give all the columns in the table names like 'PersonId_Column", "Name_Column", ...

finally, someone else who understands the inconsistency behind the table "_table" suffix

khesraw · #8 (**permalink**) 07-26-10, 00:48

yeah thanks!
but i didnt code yet, because first i want to design it i mean its E_R model suppose it is part of my faculty project .
and what u said i got that, and the exact thing is:

Person(...............
Employee(...............
Technician(.................
Nurse(................
Patient(.............

database for a hospital

khesraw · #9 (**permalink**) 07-26-10, 05:04

i mean the following relationships:

Person(Person_ID, Name...............
--Employee(Person_ID,??...............
----Technician(Person_ID,??.................
----Nurse(Person_ID,??................
--Patient(Person_ID,??.............

now can i add the Person_ID of Employee to the NUrse and Technician Person_ID column???

and what about the Name column in those, do u have some idea
thanks from your help again.