This can be a quite simple question (or at best in the start it ought to be).
I'm considering good ways to model a 1 to a lot of relationship in which the many is identical type.
Say there exists a Person Entity, which person has to a lot of Sub-Persons, and also the Sub-Persons have or 1 Parent-Person.
The solution should consider read optimisation &lifier querying simplicity from Linq to Entity Framework. Solutions relevant to underlying Database table structure for the best read performance will also be very welcome (as lengthy as they possibly can be planned to by EF4).
This appears like a situation of self-referential foreign key. (oracle)
A generally used example may be the Worker-Manager relationship. Confirmed worker could be a manager (with other employees) and may in-turn possess a manager (or otherwise, if he's in charge).
The table definition and constraint definition could be such as this.
CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) )
and also the constraint could be ..
alter table emp add constraint fk_emp_mgr foreign key mgr references emp(empno);
This signifies the manager Id for any given worker must themself be an worker. This is actually the sample data.
EMPNO ENAME JOB MGR 7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782
As you can tell, all employees possess a manager (except KING who's the business's boss.). all of them is within turn an worker.
Observe that this can be a model that's appropriate and excellent for OLTP style systems. There's no redundant data and data integrity constraints are taken proper care of.