Results 1 to 12 of 12

Thread: Bridge Table

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Bridge Table

    Hello evreyone I have a problem that I tried to solve by making two tables (Student and Parent) into one table called People but the boss doesnt want it that way he wants it to stay Studet and Parent. I need to create a bridge table taht lets the users know that the Parent and Student are Related. How am I going to do that?? See apparently the original person who created the databae before used a Natural Key that the user entered called Family ID and they would like
    to keep it that way. This is what I have so far, the [Student ID] is the primary key in the Student tabe and the [Parent ID] is the primary key in the People table. But I dont think I'm doing this correctly can anyone help pleae??



    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Relationship_tbl](
    	[FamilyID] [nvarchar](50) NULL,
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Student ID] [nvarchar](50) NULL,
    	[RelationshipID] [int] IDENTITY(1,1) NOT NULL,
    	[TypeofRelationship] [nvarchar](50) NULL,
    	[Description] [nvarchar](50) NULL,
     CONSTRAINT [PK_Relationship_tbl] PRIMARY KEY CLUSTERED 
    (
    	[RelationshipID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also posted here: Bridge Table
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Oh, my all the different definitions of "family" are going to rear up, now, aren't they. Does family extend to grandparents? Stepparents? Adoptive parents? Godparents? Legal Guardians? Family friends approved to pick up the kid after school? And let's not get started on a teen pregnancy enrolling their child in Kindergarten (OK. That last one should be fairly rare).

    What does the query look like when you try to get all children of a parent? Then all parents of a child? I see a mapping table here, are there separate Parent and Student tables?

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by MCrowley
    Oh, my all the different definitions of "family" are going to rear up, now, aren't they. Does family extend to grandparents? Stepparents? Adoptive parents? Godparents? Legal Guardians? Family friends approved to pick up the kid after school? And let's not get started on a teen pregnancy enrolling their child in Kindergarten (OK. That last one should be fairly rare).

    What does the query look like when you try to get all children of a parent? Then all parents of a child? I see a mapping table here, are there separate Parent and Student tables?

    Hi Mccrowley yes and originaly thats what I was trying to prepare for is all the different relationships, but now I'm not sure how.


    People_tbl

    Code:
    CREATE TABLE [dbo].[People_tbl](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[StudtID] [nvarchar](50) NULL,
    	[Date of Referral] [nvarchar](50) NULL,
    	[FirstName] [nvarchar](50) NULL,
    	[LastName] [nvarchar](50) NULL,
    	[SS#] [nvarchar](50) NULL,
    	[DOB] [datetime] NULL,
    	[Sex] [nvarchar](50) NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL CONSTRAINT [DF_People_tbl_E Mail Address]  DEFAULT (N'Email Address'),
    	[Tribal Affiliation] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANF staff making Referral] [nvarchar](50) NULL,
    	[ReferralLocation] [nvarchar](50) NULL,
    	[RegistrationDate] [datetime] NULL,
    	[Type Participant] [nvarchar](50) NULL,
    	[ScannedDocuments] [ntext] NULL CONSTRAINT [DF_People_tbl_ScannedDocuments]  DEFAULT (N'ScannedDocuments'),
    	[EnrollmentStatus] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[WPH] [nvarchar](50) NULL,
     CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
    (
    	[Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Student_ind

    Code:
    CREATE TABLE [dbo].[Student_ind](
    	[Student ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[Referral Location] [nvarchar](50) NULL,
    	[Referral Date] [datetime] NULL,
    	[Registration Date] [datetime] NULL,
    	[Student First Name] [nvarchar](50) NULL,
    	[Student Last Name] [nvarchar](50) NULL,
    	[Student SS#] [nvarchar](50) NULL,
    	[Student Sex] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[Student Date of Birth] [datetime] NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANFreferral] [nvarchar](255) NULL,
    	[Enrollmentstatus] [nvarchar](255) NULL,
    	[EmailAddress] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_EmailAddress]  DEFAULT (N'Email Address'),
    	[ScannedDocuments] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_ScannedDocuments]  DEFAULT (N'Scanned Documents'),
     CONSTRAINT [PK_Student_ind] PRIMARY KEY CLUSTERED 
    (
    	[Student ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Now I am confused as to which is the existing setup, and what is proposed.

    I also see someone likes nvarchar(50) ;-).

    If you are designing this from scratch, take a step back, and on a piece of paper, sketch out what the objects that you are interested in are. Then work out if those objects have many to one, many to many, or one to one relationships. Worry about all the attributes of each object a bit later on.

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    the people_tbl was he one I wanted to create originally. Now what was originally in the database was two tables one called Parent the other student
    yes your right I have too many Nvarchars in there.
    Code:
    CREATE TABLE [dbo].[Parent_ind](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[Date of Referral] [nvarchar](50) NULL,
    	[Parent First Name] [nvarchar](50) NULL,
    	[Parent Last Name] [nvarchar](50) NULL,
    	[Parent SS#] [nvarchar](50) NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL,
    	[Tribal Affiliation] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANF staff making Referral] [nvarchar](50) NULL,
    	[Type Participant] [nvarchar](50) NULL,
    	[Required hours] [int] NULL,
    	[Special Instrution] [nvarchar](max) NULL,
    	[GED] [bit] NOT NULL,
    	[High School Diploma] [bit] NOT NULL,
    	[Drivers License] [bit] NOT NULL,
    	[Assessement Date] [bit] NOT NULL,
    	[Assessement] [datetime] NULL,
    	[Career Assessment Date] [bit] NOT NULL,
    	[Career Assessment] [datetime] NULL,
    	[Other] [bit] NOT NULL,
    	[Explain Other] [nvarchar](50) NULL,
    	[GED/High School Diploma-VC Adult School] [nvarchar](50) NULL,
    	[Higher Education] [nvarchar](50) NULL,
    	[Culture] [nvarchar](50) NULL,
    	[Community Service] [nvarchar](50) NULL,
    	[Vocational] [nvarchar](50) NULL,
    	[DMV] [nvarchar](50) NULL,
    	[Other 1] [nvarchar](50) NULL,
    	[Other 2] [nvarchar](50) NULL,
    	[Other 3] [nvarchar](50) NULL,
    	[Other 4] [nvarchar](50) NULL,
     CONSTRAINT [PK_Parent_ind] PRIMARY KEY CLUSTERED 
    (
    	[Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    student
    Code:
    CREATE TABLE [dbo].[Student_ind](
    	[Student ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[Parent ID] [nvarchar](50) NULL,
    	[Referral Location] [nvarchar](50) NULL,
    	[Referral Date] [datetime] NULL,
    	[Registration Date] [datetime] NULL,
    	[Student First Name] [nvarchar](50) NULL,
    	[Student Last Name] [nvarchar](50) NULL,
    	[Student SS#] [nvarchar](50) NULL,
    	[Student Sex] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[Student Date of Birth] [datetime] NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANFreferral] [nvarchar](255) NULL,
    	[Enrollmentstatus] [nvarchar](255) NULL,
    	[EmailAddress] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_EmailAddress]  DEFAULT (N'Email Address'),
    	[ScannedDocuments] [nvarchar](50) NULL CONSTRAINT [DF_Student_ind_ScannedDocuments]  DEFAULT (N'Scanned Documents'),
     CONSTRAINT [PK_Student_ind] PRIMARY KEY CLUSTERED 
    (
    	[Student ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, the way they are accustomed to doing this is to simply query each table based on FamilyID. This will get a tad problematic, if someone with kids divorces, remarries, and adopts their new spouse's kids. That person would then be a member of two families. Using a People table, all students and parents would likely be in a single table. There may be separate tables for student attributes, or parent attributes, but there is a lot of overlap. Depending on scale (likely very small for this), you will probably get advice both ways. you would likely have a relationship table that would look like

    Code:
    create table relationships
    (Person1 nvarchar(50) not null,
     Person2 nvarchar(50) not null,
     RelationshipType not null
     constraint PK_Relationship primary key (person1, person2))
    Yes, someone could be their own grandfather with this, but that theoretically can happen, anyway.

    With the existing tables, you could make a similar table, but instead of person 1 and 2, you would have Parent and Student.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank you McCrowley thats what I was told originally also, but since the boss wants to keep the two tables I was unsure as to how to apply the Relationship table. Ok so my other question is, (and I know its silly) how would the user use this table. So they enter a record in both tables now comes the Relationship table, would they enter in both the parent and the student ID everytime (especially since it doesnt allows nulls) and then like a type or relationship (via father, mother..etc)??

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, the application should handle at least some of that for the users.

    But yes, the user would (knowingly or not) enter both the parent and student ID's as well as the relationship type in the third table.

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by MCrowley
    Well, the application should handle at least some of that for the users.

    But yes, the user would (knowingly or not) enter both the parent and student ID's as well as the relationship type in the third table.

    Can you explain further on the application should handle at least some of that for the users?? What do you mean (sorry I'm still learning)

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Whatever application sits between the users and the database (and there definitely should be one) should help the user navigate the data. The database and the application keep the data consistent, and translates the data for the user(s).

    Think of this, when you go to Amazon.com, you enter a userID. This is probably the PK of the Users table in Amazon's database. I say "probably", because we don't know what their database looks like. The web application has presented the data to you in a friendly way. When you select a book to put in your cart, you don't select it by ISBN. The application silently maps the title/author combination into whatever primary key is on the "Books" table. I doubt that they use Title and Author as a primary key, because ISBN seems to me a better choice, but that may just be me.

    Make any better sense, now?

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    yes thank you, the application is MS access 2007 which is what they prefer

Posting Permissions

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