Results 1 to 4 of 4
  1. #1
    Join Date
    May 2015
    Posts
    2

    Unanswered: 1-N Relationship can be table-needed

    Hi buddies!
    It's our master question, Is there any situation which a 1-n relationship needs table? if yes, bring an example.
    I know in a normal situation it doesn't need table, put "1" side foreign key in "n" side table is all we need.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sure that you've covered at least some of the cases in your classes where a table is needed for the relationship. If your instructor chose this as a master question, they don't expect much of their students.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2015
    Posts
    2

    1-n

    thank you! I noticed that i don't talk about N-N relationship which absolutely need a table to conjunction, I talk about 1-N relationships which as far as I've seen they don't need a conjunction table, because I could link the record to the other record in the other table with a foreign key to that table, so I hadn't needed any table.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I know of one situation where it is beneficial to design a 1-N relationship with an extra table: in the case of self-references. Suppose the PERSON table.
    Code:
    CREATE TABLE PERSON(
    	id	int	identity	not null	primary key,
    	first_name	NVARCHAR(100),
    	last_name	NVARCHAR(100),
    	father_id	int	not null	foreign key references PERSON(id),
    	mother_id	int	not null	foreign key references PERSON(id)
    )
    When you try to populate this table, you will notice how hard this is. You will have to find the Adam's and Eve's in the data and feed those first to the system, then the Cain's and Abel's, and so forth.

    It is way easier to design it like this:
    Code:
    CREATE TABLE PERSON(
    	id	int	identity	not null	primary key,
    	first_name	NVARCHAR(100),
    	last_name	NVARCHAR(100)
    )
    
    CREATE TABLE RELATION(
    	id	int	identity	not null	primary key,
    	child_id	int	not null	foreign key references PERSON(id),
    	parent_id	int	not null	foreign key references PERSON(id),
    	ind_relation	CHAR(1)	NOT NULL
    		CONSTRAINT CC_RELATION_ind_relation CHECK (ind_relation IN ('F', 'M')) --'F'ather or 'M'other)
    )
    Now you can populate the PERSON table in any sequence you like, and you can also populate the RELATION table in any sequence you like.

    Question:
    - How can you instruct the database to not accept more than one 'Father' or 'Mother' for one child
    - How can you instruct the database to not accept that one person is both the 'Father' and the 'Mother' of one child
    - How can you instruct the database to not accept that the parent is the same person as the child? And would that be smart? Why? / Why not?
    Last edited by Wim; 05-22-15 at 08:14.
    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

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
  •