Mixing
Database Design: Assigning a File to multiple Users
I am currently creating a Filesharing System for learning reasons in Laravel. Currently it is possible to upload Files and then to Download them later. Now I would like to extend it in a way that one File can be downloaded by multiple (but not all) persons.
Currently I have a User Table and the user id is referenced in a File Table. That way I know which File belongs to which User.
The file Table looks kinda like this:
id | filename | user_id |
1 | doc.php | 2 |
2 | fly.php | 4 |
3 | dog.jpg | 3 |
4 | cat.gif | 2 |
And so forth... That way I can just check if the user_id is the same as the Authenticated and logged in User.
The problem is I couldnt quite figure out how to make a file accessible to multiple users (10-20).
Do I just create a new Table which will look like this:?
user_id | file_id_1 | file_id_2 |
1 | 3 | 6 |
And everytime a additional file is assigned to a User a new Column is created?
To me this seems like very bad Data Schematic.
php database laravel database-design phpmyadmin
asked Nov 19 '18 at 23:04
T.KT.K
386
add a comment |
I am currently creating a Filesharing System for learning reasons in Laravel. Currently it is possible to upload Files and then to Download them later. Now I would like to extend it in a way that one File can be downloaded by multiple (but not all) persons.
Currently I have a User Table and the user id is referenced in a File Table. That way I know which File belongs to which User.
The file Table looks kinda like this:
id | filename | user_id |
1 | doc.php | 2 |
2 | fly.php | 4 |
3 | dog.jpg | 3 |
4 | cat.gif | 2 |
And so forth... That way I can just check if the user_id is the same as the Authenticated and logged in User.
The problem is I couldnt quite figure out how to make a file accessible to multiple users (10-20).
Do I just create a new Table which will look like this:?
user_id | file_id_1 | file_id_2 |
1 | 3 | 6 |
And everytime a additional file is assigned to a User a new Column is created?
To me this seems like very bad Data Schematic.
php database laravel database-design phpmyadmin
asked Nov 19 '18 at 23:04
T.KT.K
386
3
You need to have afiles_userstable withfile_idanduser_idcolumns. Find all the rows wherefile_idmatches a particular file to get a list of who can access it. Find all the rows whereuser_idmatches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.
– Greg Schmidt
Nov 19 '18 at 23:11
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
1
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55
add a comment |
I am currently creating a Filesharing System for learning reasons in Laravel. Currently it is possible to upload Files and then to Download them later. Now I would like to extend it in a way that one File can be downloaded by multiple (but not all) persons.
Currently I have a User Table and the user id is referenced in a File Table. That way I know which File belongs to which User.
The file Table looks kinda like this:
id | filename | user_id |
1 | doc.php | 2 |
2 | fly.php | 4 |
3 | dog.jpg | 3 |
4 | cat.gif | 2 |
And so forth... That way I can just check if the user_id is the same as the Authenticated and logged in User.
The problem is I couldnt quite figure out how to make a file accessible to multiple users (10-20).
Do I just create a new Table which will look like this:?
user_id | file_id_1 | file_id_2 |
1 | 3 | 6 |
And everytime a additional file is assigned to a User a new Column is created?
To me this seems like very bad Data Schematic.
php database laravel database-design phpmyadmin
asked Nov 19 '18 at 23:04
T.KT.K
386
I am currently creating a Filesharing System for learning reasons in Laravel. Currently it is possible to upload Files and then to Download them later. Now I would like to extend it in a way that one File can be downloaded by multiple (but not all) persons.
Currently I have a User Table and the user id is referenced in a File Table. That way I know which File belongs to which User.
The file Table looks kinda like this:
id | filename | user_id |
1 | doc.php | 2 |
2 | fly.php | 4 |
3 | dog.jpg | 3 |
4 | cat.gif | 2 |
And so forth... That way I can just check if the user_id is the same as the Authenticated and logged in User.
The problem is I couldnt quite figure out how to make a file accessible to multiple users (10-20).
Do I just create a new Table which will look like this:?
user_id | file_id_1 | file_id_2 |
1 | 3 | 6 |
And everytime a additional file is assigned to a User a new Column is created?
To me this seems like very bad Data Schematic.
php database laravel database-design phpmyadmin
php database laravel database-design phpmyadmin
asked Nov 19 '18 at 23:04
T.KT.K
386
asked Nov 19 '18 at 23:04
T.KT.K
386
asked Nov 19 '18 at 23:04
T.KT.K
386
asked Nov 19 '18 at 23:04
T.KT.K
386
asked Nov 19 '18 at 23:04
T.KT.K
386
386
3
You need to have afiles_userstable withfile_idanduser_idcolumns. Find all the rows wherefile_idmatches a particular file to get a list of who can access it. Find all the rows whereuser_idmatches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.
– Greg Schmidt
Nov 19 '18 at 23:11
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
1
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55
add a comment |
3
You need to have afiles_userstable withfile_idanduser_idcolumns. Find all the rows wherefile_idmatches a particular file to get a list of who can access it. Find all the rows whereuser_idmatches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.
– Greg Schmidt
Nov 19 '18 at 23:11
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
1
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55
3
3
You need to have a
files_users table with file_id and user_id columns. Find all the rows where file_id matches a particular file to get a list of who can access it. Find all the rows where user_id matches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.– Greg Schmidt
Nov 19 '18 at 23:11
You need to have a
files_users table with file_id and user_id columns. Find all the rows where file_id matches a particular file to get a list of who can access it. Find all the rows where user_id matches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.– Greg Schmidt
Nov 19 '18 at 23:11
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
1
1
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55
add a comment |
1 Answer
1
active
oldest
votes
K
As @Greg has already commented you are best to use a pivot table to link the files and users to each other.
This will allow you to use hasManyThrough relationship to get all the files that are accessable by a user and all the users that have access to a file
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
answered Nov 20 '18 at 0:37
JoshJosh
720111
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
add a comment |
Your Answer
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%2f53383904%2fdatabase-design-assigning-a-file-to-multiple-users%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
K
As @Greg has already commented you are best to use a pivot table to link the files and users to each other.
This will allow you to use hasManyThrough relationship to get all the files that are accessable by a user and all the users that have access to a file
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
answered Nov 20 '18 at 0:37
JoshJosh
720111
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
add a comment |
K
As @Greg has already commented you are best to use a pivot table to link the files and users to each other.
This will allow you to use hasManyThrough relationship to get all the files that are accessable by a user and all the users that have access to a file
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
answered Nov 20 '18 at 0:37
JoshJosh
720111
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
add a comment |
K
As @Greg has already commented you are best to use a pivot table to link the files and users to each other.
This will allow you to use hasManyThrough relationship to get all the files that are accessable by a user and all the users that have access to a file
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
answered Nov 20 '18 at 0:37
JoshJosh
720111
K
As @Greg has already commented you are best to use a pivot table to link the files and users to each other.
This will allow you to use hasManyThrough relationship to get all the files that are accessable by a user and all the users that have access to a file
https://laravel.com/docs/5.7/eloquent-relationships#has-many-through
answered Nov 20 '18 at 0:37
JoshJosh
720111
answered Nov 20 '18 at 0:37
JoshJosh
720111
answered Nov 20 '18 at 0:37
JoshJosh
720111
answered Nov 20 '18 at 0:37
JoshJosh
720111
720111
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
add a comment |
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
BelongsToMany, not hasManyThrough
– Devon
Nov 20 '18 at 0:39
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
@Devon BelongstoMany can be used to make a hasManyThrough relationship. hasManyThrough allows for the relationship direct from users to files and reverse but requires the relationships between all the models already be created
– Josh
Nov 20 '18 at 0:55
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%2f53383904%2fdatabase-design-assigning-a-file-to-multiple-users%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
3
You need to have a
files_userstable withfile_idanduser_idcolumns. Find all the rows wherefile_idmatches a particular file to get a list of who can access it. Find all the rows whereuser_idmatches a particular user to get a list of the files they can see. This is called a "many to many" relationship, you'll come across it a lot in Laravel.– Greg Schmidt
Nov 19 '18 at 23:11
Thank you very much Greg. I will look into it.
– T.K
Nov 19 '18 at 23:15
1
laravel.com/docs/5.6/eloquent-relationships#many-to-many
– miken32
Nov 19 '18 at 23:55