Depending on “how different are contact details” (at least 50% common) and number of each group records, i would think about place them all in single table, add field ‘clients/Candidats/Users’ and define three views - no need to worry about links etc… Use sync view for cases where you want let collaborator to work with table1, restricting table2
Depending on “how different are contact details” (at least 50% common) and number of each group records, i would think about place them all in single table, add field ‘clients/Candidats/Users’ and define three views - no need to worry about links etc… Use sync view for cases where you want let collaborator to work with table1, restricting table2
Common fields are a b c d e f g h
Fields applicable for table 2 are k l m n o p
Fields applicable for table 3 are s t u v w x
Are you suggesting I have one table with fields a b c d e f g h k l m n o p t u v w x?
Ok, my opinion. Depending on what you ultimately want, it may be better to have all this information in one table, like @Alexey_Gusev mentioned in his post. You can select applicable fields by view, so repeat fields get filled out every time and just make a field to categorize the record (client, candidate, user). You also eliminate the need to create 2 records, since you have populated the “common” fields. The drawback to this approach, is when you link a record to another in the same table. You will be able to see the link in only 1 direction. That is really the only drawback I see to having 1 table.
Common fields are a b c d e f g h
Fields applicable for table 2 are k l m n o p
Fields applicable for table 3 are s t u v w x
Are you suggesting I have one table with fields a b c d e f g h k l m n o p t u v w x?
Well, it depends on what we call “common”. it’s not about “field name match”, rather - a similar field format and (maybe) some common meaning of data. for example, if you have field “photo” for table 1, “personal document”(pdf) for table 2, and nothing for table 3, you can join it all as "attachment’ field.
anyway, working via views, even if your data has no common columns at all, except primary, you can still do it, working, like you have several tables.
choose several tables is when you see clear reason for it (example: table 1 must be shared, while table 2 not allowed for view. or you have need to create some linked relations, where each record table 1 in some way should be logical linked to records from table 2).
By the way, avoid redundant linking. Base, when each table connected to others, instead of using “star” of “snowflake” schema, tending to turn into chaotic mess of 100 fields where 80-90 are links and lookups, with geometric progression speed.
I can’t attach more detailed table (nda reasons), one of mine tables for example. red arrow is “group” (like Candidates, Users in your case) . It’s a small filtered set of the whole table, just to fit all 4 groups on screen.
