-2

Here we have a fake company, a Blood Bank. The core idea is that only donors can donate blood but cannot log into the system. However, a "registered user" (rows in the user table) that represents a company can log into the system and see the amount of blood that their company has donated. Donors must be connected to companies. In a fringe case, a "registered user" can also donate blood.

  • User = A "registered user". Can log in.
  • Donor = Cannot log in.
  • Admin = A site administrators. Can log in.
  • Blood bank employee = Self explanatory. Can log in.

There might be other types of users in the future, like differentiating types of "registered users". Maybe, just maybe.

SOLUTION 1

  • Separate donor table.

enter image description here

PROS:

  • Queries to find donors will be faster, especially if the table grows large

CONS:

  • What to do if a donor suddenly wants to log in? Create a duplicate entry in the user table?
  • What if a "registered user" wants to donate? Create a duplicate entry in the donor table?

SOLUTION 2

Use ACL role/user_role tables to define donors (and other user types)

enter image description here

PROS:

  • Easy to handle a donor that wants to later login as a "registered user"
  • Easy to handle a "registered user" that later wants to become a donor
  • Also easier to promote any user to an admin

CONS:

  • There are fields that donors do not need, like 'password', 'throttled', so There will be extra NULLs

SOLUTION 3

Identical to Solution 2, except creating an additional table user_type. This would be done to avoid re-using the ACL system for controlling log in & user account type details.

SOLUTION 4

Aggregate user. enter image description here

This is based on user1759572's suggestion to use an aggregate user. I may not have modeled it exactly right.

Which option would you go with? Is there a 4th.. 5th option.. something better?


Any reply is very much appreciated. This will help me nail down a final bit of design that I've been bouncing around on for a few days now. Arg. Thanks you SO!

TylerH
  • 20,799
  • 66
  • 75
  • 101
Tabby Laredo
  • 177
  • 1
  • 3
  • 9
  • Do you search for the best way or a pragmatic one? – user1759572 Sep 02 '13 at 21:24
  • My hope is that they are the same. Probably the best way, or at least what doesn't violate any obvious design best practices that I might have overlooked – Tabby Laredo Sep 02 '13 at 21:27
  • 1
    Could you explain your domain logic here. From the 1 as I see only companies manage the donors and their blood? yes? It it a requirement now? – user1759572 Sep 02 '13 at 21:35
  • One more way would be aggregate USERS table to other entities and have something like `Company [1-*] Donor, Company [1-1] User, Donor [1-1] User, User [1-*] ACL rules ` – user1759572 Sep 02 '13 at 21:39
  • In the roles above, Admins, Users, "Blood bank employee" would be able to manage donors and their blood. However, a User would only be able to manage his/her own companies donors, where Admins and "Blood bank employee" could manage any donor. Yes, this would be a requirement. A "soft" requirement – Tabby Laredo Sep 02 '13 at 21:42
  • So what does that mean. Company has people, some of the people are donors, some are users and some are both. So in other words you have 2 ways: 1.Aggregate Person to a User and a Donor.(you have some users, if you know some info on them create person row. you have some donors - add it to donors table. If you know more info on that donor create row person. if user wants to donate, just connect donor to a person, if a donor wants to be user, just connect person to a user.) so in other way, user now is like credentials - don't have credentials cant login :) – user1759572 Sep 02 '13 at 21:54
  • Way 2. If you think a user is "something" that uses a system and a donor is "something" that gives blood then you have another way, you just create that something and add a flags to it. So "Something" now has a Donor flag. If it needs to login "Something" set a User flag(with credentials) and its ready to go. so these 2 are similar, but the logic changes... – user1759572 Sep 02 '13 at 22:02
  • user1759572, im still trying to wrap my head around aggregate person method you described, particularly why the Company-User connection is 1-1. I will add that option the original post soon. Way 2 sounds a lot like Solution2 in the original post, where the `user` table is the "something" and the `role` table defines the flags that allows donor or login status. The main problem I have with that is that many of the fields in a "something/user" table, like throttled, password, would be null for a donor type of user. I hope I understood your comment correctly. Thank you for the quick responses – Tabby Laredo Sep 02 '13 at 22:10
  • i am sorry I did a mistake in that comment.... It should be `Company [1-*] Donor, Company [1-*] Person, Person [1-1] User, Donor [1-1] User, User [1-*] ACL rules`(this is kind of 3 of my suggested ways) And yes 2 way is kind of yours :) – user1759572 Sep 02 '13 at 22:23
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36684/discussion-between-user1759572-and-stabby-lorenzo) – user1759572 Sep 02 '13 at 22:24
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jan 14 '19 at 09:35
  • 1
    Please clarify via edits, not comments. Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. – philipxy May 19 '19 at 22:36

1 Answers1

0

  1. Use the Party Model and Party Role model. An individual or organization (such as a company) inherit from an abstract Legal Party. A party can play many roles, such a Donor, Staff.

  2. Instead of rolling your own user and group logins, I would use a database that supports real users and groups, and updatable views, WITH CHECK OPTION.

Community
  • 1
  • 1
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152