Mixing
Array of foreign keys in one column
I have two different types of objects, let’s say event and person. Each person can attend multiple events. I want to have two tables
event
- id [int (4),primary]
- name [varchar (30)]
- …
person
- id [int (4),primary]
- name [varchar (30)]
- mail [varchar (60),unique]
- attends [Array of events.id]
Therefore, the field person.attends should be an array of integers of length 4 and I want to tell MySQL that each value in this array is a foreign key from event.id. Is there any way to do this?
mysql phpmyadmin
asked Jan 2 at 22:10
FKranholdFKranhold
1053
add a comment |
I have two different types of objects, let’s say event and person. Each person can attend multiple events. I want to have two tables
event
- id [int (4),primary]
- name [varchar (30)]
- …
person
- id [int (4),primary]
- name [varchar (30)]
- mail [varchar (60),unique]
- attends [Array of events.id]
Therefore, the field person.attends should be an array of integers of length 4 and I want to tell MySQL that each value in this array is a foreign key from event.id. Is there any way to do this?
mysql phpmyadmin
asked Jan 2 at 22:10
FKranholdFKranhold
1053
1
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48
add a comment |
I have two different types of objects, let’s say event and person. Each person can attend multiple events. I want to have two tables
event
- id [int (4),primary]
- name [varchar (30)]
- …
person
- id [int (4),primary]
- name [varchar (30)]
- mail [varchar (60),unique]
- attends [Array of events.id]
Therefore, the field person.attends should be an array of integers of length 4 and I want to tell MySQL that each value in this array is a foreign key from event.id. Is there any way to do this?
mysql phpmyadmin
asked Jan 2 at 22:10
FKranholdFKranhold
1053
I have two different types of objects, let’s say event and person. Each person can attend multiple events. I want to have two tables
event
- id [int (4),primary]
- name [varchar (30)]
- …
person
- id [int (4),primary]
- name [varchar (30)]
- mail [varchar (60),unique]
- attends [Array of events.id]
Therefore, the field person.attends should be an array of integers of length 4 and I want to tell MySQL that each value in this array is a foreign key from event.id. Is there any way to do this?
mysql phpmyadmin
mysql phpmyadmin
asked Jan 2 at 22:10
FKranholdFKranhold
1053
asked Jan 2 at 22:10
FKranholdFKranhold
1053
asked Jan 2 at 22:10
FKranholdFKranhold
1053
asked Jan 2 at 22:10
FKranholdFKranhold
1053
asked Jan 2 at 22:10
FKranholdFKranhold
1053
1053
1
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48
add a comment |
1
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48
1
1
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48
add a comment |
2 Answers
2
active
oldest
votes
you need to have bridge table for many-to-many relationships.
thus, add PersonEvent Table to your model
PersonEvent
- EventId [int (4),primary]
- PersonId [int (4),primary]
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
add a comment |
This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that
relationship, you need a bridge table, like :
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.
Now, say that you want to list the names of all persons that participated a given event :
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';
answered Jan 2 at 23:11
GMBGMB
20.6k41028
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013842%2farray-of-foreign-keys-in-one-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
you need to have bridge table for many-to-many relationships.
thus, add PersonEvent Table to your model
PersonEvent
- EventId [int (4),primary]
- PersonId [int (4),primary]
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
add a comment |
you need to have bridge table for many-to-many relationships.
thus, add PersonEvent Table to your model
PersonEvent
- EventId [int (4),primary]
- PersonId [int (4),primary]
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
add a comment |
you need to have bridge table for many-to-many relationships.
thus, add PersonEvent Table to your model
PersonEvent
- EventId [int (4),primary]
- PersonId [int (4),primary]
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
you need to have bridge table for many-to-many relationships.
thus, add PersonEvent Table to your model
PersonEvent
- EventId [int (4),primary]
- PersonId [int (4),primary]
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
answered Jan 2 at 22:39
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
15.7k22042
add a comment |
add a comment |
This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that
relationship, you need a bridge table, like :
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.
Now, say that you want to list the names of all persons that participated a given event :
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';
answered Jan 2 at 23:11
GMBGMB
20.6k41028
add a comment |
This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that
relationship, you need a bridge table, like :
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.
Now, say that you want to list the names of all persons that participated a given event :
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';
answered Jan 2 at 23:11
GMBGMB
20.6k41028
add a comment |
This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that
relationship, you need a bridge table, like :
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.
Now, say that you want to list the names of all persons that participated a given event :
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';
answered Jan 2 at 23:11
GMBGMB
20.6k41028
This is not the proper way to proceed. You need to think in terms of database normalization : you have a relationship between person and event where each person may have many events, and each event can have many persons. To model that
relationship, you need a bridge table, like :
CREATE TABLE attendance (
id INT AUTO_INCREMENT,
person_id INT NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY fk_person (person_id) REFERENCES person (id),
FOREIGN KEY fk_event (event_id) REFERENCES event (id)
);
ALTER TABLE attendance
ADD CONSTRAINT attendance_pk UNIQUE (person_id, event_id);
The bridge table contains an autoincremented id, foreign keys that reference the 2 other tables, and a unique constraint to prevent duplicates.
Now, say that you want to list the names of all persons that participated a given event :
SELECT p.name
FROM
event AS e
INNER JOIN attendance AS a ON a.event_id = e.id
INNER JOIN person AS p ON p.id = a.person_id
WHERE e.name = 'The Foo Event';
answered Jan 2 at 23:11
GMBGMB
20.6k41028
answered Jan 2 at 23:11
GMBGMB
20.6k41028
answered Jan 2 at 23:11
GMBGMB
20.6k41028
answered Jan 2 at 23:11
GMBGMB
20.6k41028
20.6k41028
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013842%2farray-of-foreign-keys-in-one-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Instead you need a third table with event id and person id to define the many-to-many relationship. Putting some sort of list of values in the attends column like that is going to cause you problems in the future. (see stackoverflow.com/questions/3653462/…)
– Don't Panic
Jan 2 at 22:12
An N:M relationship is better modelled as a third "relationship table". Do that. Otherwise, you'll have a ton of headackes.
– The Impaler
Jan 2 at 22:15
Modelling it as an array is not even 1NF.
– The Impaler
Jan 2 at 22:15
First problem you're likely to encounter: how to select all people attending a specific event.
– Don't Panic
Jan 2 at 22:18
For more kinds of problems, read my old answer to Is storing a delimited list in a database column really that bad?
– Bill Karwin
Jan 2 at 22:48