0

Hello everyone,

I'm planning to change the current backend for my company. I'm planning to create new web service with asp.net core and ef core. To be honest I'm stuck with the architecture.

I don't know how should I implement the relationships between the tables for Entity Framework. There is more than one positions as employees. And I don't want to implement a role layer for this architecture. So I've created tables like this: Employees, Cashiers, Packers, ... etc. And designed them like this: Employees (EmployeeId, EmployeeName, ...) Cashiers (EmployeeId, Fee, ...) But I've read somewhere there is no way to use 1 to 1 relationships in sql. So how can I implement this relationship?

ER Diagram: Entity Relationships

metebyte
  • 11
  • Nothing wrong with 1-to-1 relationships in SQL. Why do you say that? – The Impaler May 25 '18 at 14:49
  • @TheImpaler I've read it in here https://stackoverflow.com/questions/10292355/how-do-i-create-a-real-one-to-one-relationship-in-sql-server I mean I'm just confused. – metebyte May 25 '18 at 14:54
  • The separate tables you've created doesn't seem to add special property. If that's the data you need, you could simply have single table `Employee` with property `Fee` and property `Position` mapped to `enum` or FK to another table describing the positions. – Ivan Stoev May 25 '18 at 15:03
  • @IvanStoev First of all thanks for your help. I was planning to use mapping enum way. And that's just a representative data for asking my question. Actually I've seperated them into tables because all the employees on the system will be accessing to the panel and will have access to made changes on another one's operations. I think It will be much easier to control these logic instead of implementing a whole role logic for all the employee positions. – metebyte May 25 '18 at 15:57
  • Google SO re database/sql subtyping/hierarchies/polymorphism. PS Please don't clarify via comments, edit your post. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. Use edit functions to inline, not link, if you have the rep--make your post self-contained. And never give a diagram without a legend/key. – philipxy May 26 '18 at 17:21

1 Answers1

0

If you are using SQL Server, then I agree, it's not possible to have a real 1-to-1 relationship.

The relatioship would need to be 1:0..1 since you need to insert one row first, and the other one second. For some minimal amount of time, the relationship would be 1:0 and then will switch to 1:1.

To implement a real 1-to-1 you'll need to use the standard SQL feature called Constraint Deferrability. Unfortunately this feature is available only in PostgreSQL and Oracle databases, as far as I know. On these databases you can truly have 1:1 relationships, since the constraint is validated not on every row insert, but at the end of the transaction.

Having said that, I wouldn't care too much. If you perform the insertion using database transactions, then I don't see why this would cause any problem. Using a process like:

  1. Start transaction.

  2. Insert on table Employee.

  3. Insert on table Cashier.

  4. Commit transaction.

This should ensure you never have a dangling employee with no related table.

The Impaler
  • 45,731
  • 9
  • 39
  • 76