I created a table to keep track of which person flipped a switch at what time. It consists of:

create table records
( name varchar not null
, changed_at timestamp with time zone not null
, organization varchar not null
, value bool not null)

To determine the latest value, I can do:

select value, name
from records
where organization=?
order by changed_at desc
limit 1;

I need to keep track of all changes to the system, hence this schema.

However, I’m considering making a separate table for organizations, and then use a foreign key constraint on records.organization.

Please critique my table choice.

  • 2
    Slightly off topic, but Value is a Reserved Word, not sure about name, but its to vauge in my opinion. – Lennart Jun 19 at 19:15
  • 2
    Does it do what you want? If yes, your table choice is good. If not, consider something else. – mustaccio Jun 19 at 19:25
  1. The table should have a primary key - you can add an auto increment field as an alternative.
  2. I would create a table Person to store persons (store the name here) and a table Organization to store organizations. Given that, I would store person_id instead of name and I would store organization_id instead of organization. I assume that the same person might flip the switch more than once. If that is not the case, then perhaps your design works as well. If the persons are employees of an organization then you don't need the organization/organization_id field, you need another table where you store the employment relationship.
  3. I would add indexes on the two FK fields added at #2 and perhaps the changed_at field, this depends on how you want to search the table, but at the very least you need to add an index for each FK field.
  4. In your post there is no information about the switch that was flipped. Does it matter? Do you have a list of switches? If you do, you might want to add to this table a switch_id to keep track of which switch was flipped. Again, only if this information matters.

Re #2, the design can vary, depending on how flexible you want it to be to accommodate future requirements. You can go nuts with it and define entities that can model organizations, divisions, business units etc. if you need to. Does it matter? It depends on the requirements and what kind of information you want to extract from this table. Do you want to classify the people that flip the switch? Then you need to add more tables. For now I would keep it as simple as possible given the current requirements and with an eye on the future requirements but don't overdo it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.