0

I've been asking around a bit on the subject matter, but I finally started to build the database tables for my company's website and ran into a bit of a hitch.

In my database, I have a table, dbo.AspNetUsers, in this table, I have two columns employee_id and company_id. Company_id is tied to the table companies.companies with a field matching the same name. The column employee_id is a foreign key for two different tables: companies.employees and employee.employees.

Inside of the employee.employees table, all the employees for my company are stored. Inside of companies.emloyees, the employees for all the other companies that access our system are stored, but my company's employees aren't stored here.

Because of this, it appears that I can't impose a foreign key constraint on the AspNetUsers table for either table. Is this true? Is there any way to bypass this?

JD Davis
  • 3,517
  • 4
  • 28
  • 61
  • What are you trying to prevent? – Mike Cheel Apr 15 '14 at 19:07
  • I'm actually just trying to build the relationships between the tables. Kinda an or type thing. If company_id = 1. It should check employee.employees. If it is anything other than 1, it should check companies.employees. – JD Davis Apr 15 '14 at 19:10
  • You could add a trigger to check this although hard coding values is never a good idea. I personally would work this into my sprocs and such and not try to enforce it via contraints. – Mike Cheel Apr 15 '14 at 19:10
  • I'm not even super concerned. This is really my first database project, and I'm actually trying to avoid bad practices along the way. The only reason I was even doing it this way was to avoid having to duplicate data when I already have all of my company's employees' information stored in a couple tables for our HR schema. – JD Davis Apr 15 '14 at 19:14
  • see related here: http://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables/7844911#7844911 – nathan_jr Apr 15 '14 at 19:16
  • 1
    You need a supertype table (all_employees) for the employees, with companies.employees and employee.employees subtypes, and a FK to all_employees. – dean Apr 15 '14 at 19:19
  • @dean Can you provide a bit more elaboration upon this for me? – JD Davis Apr 15 '14 at 19:21
  • http://sqlmag.com/t-sql/sql-design-supertypes-and-subtypes – dean Apr 15 '14 at 19:24
  • http://technet.microsoft.com/en-us/library/cc505839.aspx – dean Apr 15 '14 at 19:24
  • @dean Let me see if I understand this. I'm going to have a table called `all_employees` with a PK, id, a field called type, and a field called employee_id. Inside my employee.employees table, I'm going to have a FK all_id, and the same thing inside my companies.employees. My users table will reference the all_employees id field, then based on the type of the all_employees row I can choose to return the information from the companies or employee table? – JD Davis Apr 15 '14 at 20:02
  • And a FK from AspNetUsers to all_employees, that's the idea – dean Apr 15 '14 at 20:04
  • How much extra overhead is this going to cause when I'm calling functions the entirety of my users table? I'll have a page that lists the contact information for all the users, for non employees, it's simple as they are all contained in the same table. For employees, there is a phone table and an email table that can have infinite entries for each employee, obviously this'll have to be fetched using PIVOT and UNION, but with the extra tables, how much extra work will the server be doing? – JD Davis Apr 15 '14 at 20:07
  • This will introduce just one more join in your queries, everything else stays the same. – dean Apr 16 '14 at 04:42
  • And when I'm crafting my queries, to get all the information from both tables, would I just use a where clause dictating the type? – JD Davis Apr 16 '14 at 12:57

0 Answers0