I am designing the database structure of a project and I faced the following question.
To simplify the question, let’s imagine I am developing an app that will allow to users to upload documentation related to the trips made during the year, but there is documentation related to each of the trips and documentation related to the whole year. For that it is needed a table years
and a table trips
with the following structure (same for both):
| id | description |
And now the table that will contain the documents, and here is where my question raises. I have 2 options:
Option 1: Define a unique table files
that will contain all the documentation for the years and the trips, therefore 2 foreign keys will be used, one to relate to the table years
(id column) and other one to relate to the table trips
(id column), only one value will be defined, being the other NULL:
| id | filename | data | year_id | trip_id
Option 2: Define 2 tables, files_year
and files_trips
, both with the same structure as folows, being rel_id the years
or trips
id as foreign key:
| id | filename | data | rel_id |
Which option is the right one?
Thanks!