0

I have a Boolean field in a job table called external. If it's true then the job is external and if it's false then it's internal, obviously.

If the job is external then it needs to store an ID that refers to a record in a client table, and if it's internal then it needs to store an ID that refers to a record in a staff table.

I will need the user to select whether the job is internal or external, potentially before they even know the client or staff to select. So I can't just do away with the external field, though I'm not sure if that would be a good or bad idea anyway.

It makes sense to me on the surface at least to just use one field that stores either ID rather than having two fields when only one will store data and the other will be Null. It's an ID so it's not like the datatype will need to be changed and not match in the future.

Is there any problems this could cause and is it bad practice for some reason? Because I have a feeling it could very well be.

I'm using Firestore with Flutter but I think this question is relevant to databases in general.

Tristan King
  • 97
  • 13
  • Does this answer your question? [Foreign Key to multiple tables](https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables) – philipxy Apr 02 '20 at 02:47
  • The benefit of 2/many fields is you can constrain declaratively via 2/many FKs & a check() that exactly one is null. (What I call radio button FKs.) Also the boolean/tag becomes redundant. Or computed/calculated. But that's still an anti-pattern for subtyping. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) – philipxy Apr 02 '20 at 02:49

1 Answers1

0

We had a similar scenario, where childrow could point to different parent tables. What we did was, have two fields: ReferencingTypeName(parent Table), ReferencingTypeId (PrentTable Primary key).

The problem is, we cannot create FOREIGN KEY in this scenario. We had to make sure the business logic handled it in the proper way.

Also, I feel that you dont need separate External column, as you can figure out whether a job is external from the ReferencingTypeName itself. 

+---------------------+-------------------+
| ReferencingTypeName | ReferencingTypeId |
+---------------------+-------------------+
| Client              | <ClientPrimaryId> |
| Staff               | <StaffPrimaryId>  |
+---------------------+-------------------+
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58