Not sure if I am abusing the system by trying to do this…
I want 4 tables. Table 0 is a “common” table containing basic contact details for a person.(name/email/telephone etc)
Tables 1 to 3 are clients, Candidates, and Users.
These table want to reuse the fields from table 0 but will have specific fields for each.
(From an OO perspective, Table 0 is a super class and tables 1-3 are subclasses of table 0)
The reason for this is for maintenance purposes. e.g. if I want to add a facebook ID to all contacts, instead of adding it to 3 tables, i can just add it to 1 (table 0)
I have created links from tables 1-3 to table 0, but I want to make sure that when a user creates records in tables 1 to 3, they are forced to also create and populate a corresponding record in table 0.
And when a user opens a record from tables 1-3, it displays all fields from it AND table 0
If its better to get rid of table 0 and duplicate those fields in tables 1-3, is there any way to “group” them? (Basically this is asking for the best way to setup/manage tables with many columns that logically sit in groups)
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
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.
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.