-5

For scenario: Users are either active or inactive. How would create and relate tables?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
artis898
  • 15
  • 5
  • 3
    Please make an attempt to solve the problem yourself then come back with questions about what you've tried. – Forklift Feb 16 '17 at 19:01

1 Answers1

1

Include a flag column in the user table to indicate the user's status, then build views on top of this against which you run your actual queries. Maybe something like this:

create table users (
  ....
  is_active boolean not null
);

create view active_users as
select * from users where is_active = true;

create view inactive_users as
select * from users where is_active = false;

If you need more status values, then have a separate table of status possibilities with a foreign-key relationship between it and the users table.

Ray O'Donnell
  • 759
  • 4
  • 11
  • I was thinking to have two tables: Users table: userID... Subscriptions table: subID, userID, status(active or inactive) and then joined them together on userID – artis898 Feb 16 '17 at 19:26
  • @artis898: So there's a 1-to-1 relationship between subscriptions and users? If these are the only columns in the subscriptions table, it's better to keep the status column in the users table and save the cost of the join. – Ray O'Donnell Feb 16 '17 at 19:28
  • yes, that was my first instinct, but then I 'm new to databases. basically the scenario is: Users are either active or inactive. and the query that I want to run is: List the names of books belonging to all active users. – artis898 Feb 16 '17 at 19:37
  • So then can each user have many subscriptions? If so, then subscriptions have to be in another table; otherwise I'd keep the status flag in the same table. – Ray O'Donnell Feb 16 '17 at 19:38
  • Well user can only be active or (inactive - but they may re-subscribe in the future) – artis898 Feb 16 '17 at 19:44