0

I'm developing a system using .NET which will be used by multiple users. Because of that, I need to identify on the database which data belongs to each user. Explaining with an example, imagine I have the entity Product. Then each user has its own products, and so in the database on the table of products we must be able to distinguish between products of each user.

Saying that, my solution was to add on the database an extra column for each table for the user id. Now, on my code this was the same as adding on my repositories a parameter to receive the id of the user so that the repository would be able to locate the correct data. The concrete implementation of the repository to deal with relational databases just check those columns.

The problem is that to be able to get those columns available on my repository, and on my ORM (in the case EF) I needed to add on each entity one property UserID. The problem is that if I think for a while this doesn't seem like a good solution. I'm coupling domain entities with details of how to persist data. More than that, I'm coupling each entity with the way I manage the access to the data and this seems a bad approach.

So, concearning this, is there a better way to plan this? A way to make sure we can relate data to users and in the same time avoid those properties on the domain entities?

  • What about using different database for each user? Is there case where users share data in table? – Euphoric Apr 25 '14 at 4:47
  • Identifying "Ownership" of a product is not an implementation issue here, its a basic requirement of your system. Therefore the userid should be accessible to any component in your system and not hidden in a low level layer. – James Anderson Apr 25 '14 at 5:48
  • Well @Euphoric, that was my initial idea. Then I asked on stackoverflow here stackoverflow.com/questions/22701493/…. But people there convinced me it wasn't so good to do it. Indeed, if the system comes to 300 users, for instance, I started to think that it could be confusing to have 300 databases. Do you think it's really a bad idea then? Or considering multiple databases is something possible? – user1620696 Apr 25 '14 at 14:42
  • How big will each database be? 5 tables? 10 tables? 100 tables? Maintaining one more column for 5 tables is fine, but adding it to 100 tables might be a problem. Lesser problem than 300 databases. – Euphoric Apr 25 '14 at 15:45
  • Now I see your point @Euphoric. The database could really grow that large, with lots of tables. Indeed from times to times more tables would be created to add new features. It could really be a problem. So it starts to seem easier to manage more databases. I've even thought for a while about using NoSQL, but I was unsure. What you think about it? And thanks a lot for your point. – user1620696 Apr 25 '14 at 15:50
2

If each user truly has their own products, and user Joe's product named 'Foo' has nothing to do with user Jane'e product named 'Foo' (too, by pure coincidence), then the user ID does belong to your problem domain.

I don't see why you have to add a user ID column to every table that references a product. A product may have an (artificial) unique PK, and a product's user ID can be looked up in the product table using it.

If, on the other hand, your products make sense when separated from users, I assume there's an M:N relationship between products and users. A table of pairs (product_id, user_id) would suffice then.

  • Indeed each user truly has their own products, so I see the user ID really belongs to the domain. Now, what I meant by having the ID on every table, wasn't on tables referencing products. The point is that there are many other entities (Categories, Customers, etc) which also behave in the exactly the same: each user has their data. So, in that case, each of these entities would really need the user id right? – user1620696 Apr 25 '14 at 3:05
1

In this case you should have userid in entities (Categories, Customers, etc) as a FK. Instead of creating separate table for each and every user, use one table and have a FK in that table which will be linked to User main table.

However if you have customer main table then you just can have a transaction table between User and Customer.

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.