How to handle a database used by multiple companies that need to store specific and non specific data about the same member?

Question

I'm working on a database which stores data on members and staff for locations. Each location belongs to a location_group or company and the member data needs to hold values unique to that location_group. But the data held in the person and login tables which are used for both staff and members is not unique to and location or group. If a member was made for different location groups but was the same person then there would be a duplicated person entry. Is there a better way of doing this? This is the first complex database I have made so I probably have not made it the best way.

ERD of the tables related to this question, And this is the full ERD so far.

I am using an SQLite database with python as it is all that is available at my school. Thanks for any help.

Edit 1: The data being stored is personal information in the person table which is used by the staff and member tables so someone could be both a member and staff at a location meaning a staff could use the facilities at the location as a member. the member table hold member and location specific information such as which waivers they have signed and which facilities they can use.

Answer 1

Assuming that it's ok for the same person to have memberships in multiple location groups and also be on staff at multiple locations, this looks fine.

Something to be careful of is duplicate memberships. Presumably you don't want the same person to have a membership in the same location group, nor be on staff at the same location twice. You need unique indexes to enforce this.

-- Note, with this you don't need a separate index on person_id.
create unique index unique_member_idx
  on member(person_id, location_group_id);

-- And you don't need a separate index on person_id.
create unique index unique_staff_idx
  on member(person_id, location_id);

You might also want to put a unique index on person.email, being careful to normalize your email addresses (downcase them, strip leading and trailing whitespace) before inserting or as an insert/update trigger.

---

For the location settings, unless you're storing bit fields blob is incorrect; it's for binary data. You could store JSON as text, but SQLite lacks JSON functionality so querying it would be very slow. I'd recommend a key/value table for your location specific settings.

-- And one for location_group.
create table location_settings (
  location_id bigint not null references location(id),
  key text not null,
  value text not null
);

How you index this depends on how you're going to query it. If you plan on querying the value of a key for all locations like select location_id, value from location_settings where key = ? then put an index on key. Otherwise if you'll always query with where location_id = ?, even with and key = ? the index on location_id should be fine.

You can add a set of values by inputting the same key for the same location.

insert into location_settings (location_id, key, value)
  values (1, 'foo_list', 23), (1, 'foo_list', 42);

Or you can stick to one key and store complex values as JSON.

-- Do not allow duplicate keys.
create unique index location_settings_key_idx
  on location_settings(key, location_id);

insert into location_settings (location_id, key, value)
  values (1, 'foo_list', '[23, 42]');

---

A few other notes.

• Store dates as datetime or timestamp. I know SQLite doesn't have a date type, it will ignore it and store a string, but get into the habit now. And store them as ISO 8601.
• Declare everything not null unless it specifically needs to be null. This will save you a lot of headache later.
• Don't limit the size of fields in the schema. You're not saving any space. Size limits are a business rule; they change and should be enforced in the app. Use text or varchar(255).
• DO NOT store passwords in the clear. They should be salted and hashed using a library.

Answer 2

Each location belongs to a location_group

That means each location belongs to 1 (or 0 or 1) location_group. If so, your tables don't show that. location should have a foreign key to location_group, not the other way around.

member data needs to hold values unique to that location_group

That means there are are data that are unique to a member at a location that is defined by the location group. For example, all VPs in the northeast group could have offices painted green on the north wall. I've seen that.

In that case, a member_location would describe a member at a location, which perforce is in a location group. Its natural key (term of art) is 2 columns: the member ID and location ID. (If you want to know what group the member's location is in, that's what joins were made for.) That assumes a member may belong to more than one location; otherwise, location is an attribute of member, and not part of the key.

If a member was made for different location groups but was the same person then there would be a duplicated person entry

I might not understand what you mean by "person entry". If a member is associated with a location (group), that is represented by a row. Additional locations require additional rows. Each row is a distinct fact. If you can remove something "duplicated" without destroying the fact, you're on the road to normalizing your database design.

Looking at your ERD, the relationships are ... unusual. For example, a member is the product of a login and a person. I can accept that a person might have more than one login, but can one login belong to more than one person? If not, then login might have a column for the person to whom the login belongs.

One note about convention, and one about design.

• You'll find it's much more common to see foo_id or FooID or FooId than idfoo. DBMS systems rely heavily on listing information sorted alphabetically, and the users database metainformation are looking for the most important information first in the name.
• Do not blindly assign an arbitrary integer as a primary key to each row. Look for the identifying information, and use combinations of columns to form keys. That will help you decide what is identifying information and what is not, which might be the most important aspect of database design.