Database design and data consistency

Ruby on Rails
1

Hey!

At the moment I have a user and a product table and products are created by users. So the user can create his own product he is working on.
User has_many :products,
product belongs_to :user.

Now I’d like to provide the user to be able to add more users to the product (who worked on product with him/her) even if those are not registered yet in the app.

So I will change the product and user table connection to
Product has_many :users, through: :ProductUsers,
User has_many :products, through: :ProductUsers.

When the user creates a product and adds the extra name and the email of the other users in the form the app will send out emails to those people to register (only if they are not registered). I can manage this far.

So here is my problem: The product is already created with the added names and emails when the email invitation is sent out.

  • Is “the has_many through” approach for the table the good one or I should differentiate the creator and other users somehow for some authorization reasons?

  • How can I make sure that after registration the new user entity will be assigned to the product his email/name is already assigned to by the guy who created the product? / How can I make sure the data will be consistent?

  • When the product gets created what kinda table should contain the email/name of the people who are not registered yet?

2

I’d recommend only storing actual Users in the users table, and limiting its fields to aspects closely related to authentication/authorization, e.g. username, email, and password.

For the data model, you described, I would introduce a new model, perhaps called Collaborator or TeamMember. It would store the person’s name and email. It would belong_to a Product, and optionally belong_to a User.

To differentiate the creator, you would just need a boolean field on the Collaborator model.

How can I make sure that after registration the new user entity will be assigned to the product his email/name is already assigned to by the guy who created the product?

You could generate a unique token per collaborator, and use this in the sign-up invitation link.

3

Hi Andy, thanks for the super quick answer!

I don’t store anything but authentication stuff in the user table, just didn’t wanna mix my profile table in here :).

Unfortunately I don’t really get your table setup. How did you mean “optionally belongs_to a User”?

Here is the setup I think you were talking about:

Collaborator:

belongs_to :product

fields: product_id: integer, email, name, is_owner: boolean

Product:

has_many :collaborators

Is this it?

4

Yes, that’s what I had in mind. (There may be some duplicate names if some people work on multiple products. which may or may not matter depending on your use case).

Regarding the optional association, I mean that the collaborators table would have a foreign key user_id. This would be either NULL, or point to the id of a record in the user’s table.

Hope that makes sense.

5

Thanks Andy!