2

I am going to comment on the scenario that I currently have when designing my database.

I have the following tables:

user (id, name, lastname)

role (id, name)

user_role (user_id, role_id, from_date, to_date)

period (id, name, from_date, to_date)

The period table serves as a data master to know the name of the periods that I define in my application.

In this case I mention only the user, role and user_role tables, but there are many more tables that have periods.

The intention of the period table is to act as a labeling system for the different periods that exist in the tables of my model. This table is not related in any way by any foreign key with other tables. To do the searches and relate other tables to this, a join is made using dates from and dates to.

For example, a tuple of the user_role table can have a date from and a date until that covers several periods defined in the period table, so it would be labeled with the names of the periods it comprises.

Is it okay for the period table to be an unrelated table? It only serves to give names to the periods, that's why I put it this way. Can you think of another better way to raise it? Should the period table be related to the other tables in some other way?

Thanks in advance.

Mr. Mars
  • 762
  • 1
  • 9
  • 39
  • 5
    Yes, it's okay for the period table to be an unrelated table. It appears to be useful for your needs, and that's all that matters. – Gilbert Le Blanc Feb 25 '20 at 11:29
  • You could also make a date table (dateid, date) so all those compares are just int compares instead of date compares (from_date becomes from_dateid, to_date becomes to_dateid, etc). Technically this would make the period table be related to the date table. If you wanted further data integrity you could have the from_date column have a FK to the from_date column in period, and separately have the to_date column have a FK to the to_date column in period. This assumes you want to *require* that linkage – Stephen S. Feb 27 '20 at 15:25
  • 1
    @StephenS.: The common implementation of DATEs in a DBMS is based on integers, so all compares are already based on *int compares*, how should this improve anything? This table is not unrelated, it's just a relation which can't be expressed in standard Foreign Key syntax, it's a kind of *Temporal FK*. – dnoeth Feb 28 '20 at 09:26
  • How is this question not just, how do I design relational DBs & write SQL queries? – philipxy Feb 28 '20 at 16:17

5 Answers5

2

So you have two situations.

Situation one, (foreign key) You do refer to a period from your other table . Lets say table x refers to the period table through period_id. If you need to update the period for many rows that use the same period, you only have to update one row in the period table. The benefit of this solution is if you have 1000 rows in table x that refer to one period, you only have to update the period and not the 1000 rows. Also if multiple rows in multiple tables refer to the same period you only have to update one row in the period table. The disadvantage is that if you want a period that differs from the existing periods you have to create a new period and refer to the period from table x, instead of updating table x only.

Situation two you store the period in table x If you do not refer to the period table from table x it is easier to customize the period, you only have to update table x instead of the record in the period table.

So if you dont share data between many rows the benefits of situation one are non existing and situation two is better.

Sven van den Boogaart
  • 11,833
  • 21
  • 86
  • 169
2

You are making time dimension in Data-warehousing terms. And you do not need to have relationship with other tables.This table is only used for lookup and can be used based on date interval.

Digvijay S
  • 2,665
  • 1
  • 9
  • 21
1

Tables (bases, views & query results) represent relation(ship)s/associations. FK (foreign key) constraints are sometimes called "relation(ship)s" but are not. They are statements of fact. They say that subrows appear elsewhere as a PK (primary key) or UNIQUE; that entities participate elsewhere once. Table meanings are necessary & sufficient to query. Constraints--including PKs, UNIQUE, NOT NULL, CHECK & FKs--are neither necessary nor sufficient to query. They are for integrity to be enforced by the DBMS. (But when constraints hold, additional queries return the same results as queries that don't assume constraints.)

Declare constraints when they hold & are not implied by constraints already declared, and don't declare them when they don't hold or are implied by constraints already declared.

Re querying & constraints.

philipxy
  • 14,867
  • 6
  • 39
  • 83
0

period is related to user_role, they both have from_date and to_date columns which relate to one another.

They don't have a foreign key relationship but you could absolutely join your period table to your user_role table if that's what you wanted to do.

I don't think there's a better way to design this, since your periods are arbitrary, and have a many-to-many relationship to your user_roles.

Geoff Griswald
  • 937
  • 12
  • 24
0

The question if the period table can be an unrelated table is finally a business question and can only answered by you / your business requirements... ;) If e.g. your user role refers to one or many periods, there should be a FK in the user role table instead of start and end date / or, if many to many - a relation table. If the definition of periods follows a different logic and allows overlapping periods or asynchronous assignments from events to dates / periods, you are fine with unrelated.

Andreas
  • 46
  • 3