Results 1 to 10 of 10

Thread: database design

  1. #1
    Join Date
    Jul 2010
    Posts
    5

    database design

    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.

  2. #2
    Join Date
    Jul 2010
    Posts
    5

    database design

    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by khesraw View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    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', ...)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2010
    Posts
    5
    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

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2010
    Posts
    5
    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

  9. #9
    Join Date
    Jul 2010
    Posts
    5
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by khesraw View Post
    and what about the Name column in those
    the name should appear only in the Person table

    unless of course a person always changes his name when he takes up a new position, e.g. technician
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •