CODEWITHSUNDEEP
sqlreferencefieldrecordmultiple-tables
Tony Shelleman
Tony Shelleman

Reputation: 11

Can one field reference multiple records in another table?

I'm creating a database and using ASP/C# to make an app to track attendance of kids for church on buses and in the classes.

I have the database designed, but I'm wondering if there's a better way to do it. I have a table for things like address, child and parent/guardian/trusted person, etc., all linked via unique ID. For each entry in the child table, I have a Guardian1ID, Guardian2ID, Trusted1ID, Trusted2ID field that contains the ID of the parent/guardian/trusted person.

My question is this: Is there a better way of doing this? Some kids have one parent while others have both parents plus aunts, uncles, etc. Is it possible to have one field that is like a variable length array that can reference several IDs in another table?

Upvotes: 1

Views: 2718

Answers (3)

John Bollinger
John Bollinger

Reputation: 180113

My question is this: is there a better way of doing this? Some kids have one parent while others have both plus aunts, uncles, etc. Is it possible to have one field that is like a variable length array that can reference several IDs in another table?

No, not as such, not if you want the database to enforce referrential integrity as it will do with foreign key constraints. Moreover, such a structure would be grossly de-normalized, inasmuch as it would fail to be even in first normal form.

I'm inclined to suspect that you really want a many-to-many relationship between children and adults. You've talked about how any one child may have relationships with multiple adults, but surely it may also be the case that a single adult may have relationships with multiple children. You don't want to have to enter or track anyone's information multiple times. Since supporting a many-to-many relationship requires a relationship table, you can furthermore include a column on such a table that identifies the nature of each relationship (mother, father, adult sibling, weird Uncle Al, ...). In simplified form it might look like this:

create table child (
    id int not null primary key,
    name varchar(50)
);

create table adult (
    id int not null primary key,
    name varchar(50)
);

create table rel_type (
    id int not null primary key,
    description varchar(20)
);

create table trusted_relationship (
    child_id int not null,
    adult_id int not null,
    rel_type_id int not null,
    foreign key (child_id) references child(id),
    foreign key (adult_id) references adult(id),
    foreign key (rel_type_id) references rel_type(id)
);

(MySQL syntax).

Upvotes: 0

Caesar
Caesar

Reputation: 59

From my understanding Tony the guardians can be included in the child/parents table if there won't be repetition of records

Upvotes: 0

Victor - Reinstate Monica
Victor - Reinstate Monica

Reputation: 1280

Assuming that for your application the parents, aunts, guardians etc. are equal, this seems a many-to-many relation.

I'd add a Child table with ID, and name, then a ParentGuardianEtc table with ID and name, and finally a bridge table for childID and attendantID.

Given the real-life possibility that one person can be a children's parent but might also attend somebody else, like a nephew, for an occasion, and also two children might be brothers or half-brothers, this seems the most reasonable.

Then the childID and the attendantID are foreign keys, and you can also add a comment field or whatever else needed.

Upvotes: 3

Related Questions