The problem here is that this seems to be a piece of a larger project and the wording is fairly confusing. I have tried to untangle this a bit, but I have way more questions than answers. However, your main concern seem to be constraints, so this example should help. The main problem is reliance on single-column keys -- not necessarily bad in itself, but hard to get constraints right.
Keep in mind that this is a logical design, that will not directly solve your problem as stated, but if you "convert" it to PostgreSQL, it will work. This way you can experiment with constraints and adjust your project.
Note:
All attributes (columns) NOT NULL
PK = Primary Key
AK = Alternate Key (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Custom type for service type ID (discriminator); use this type for every SVC_TYP_ID column.
TYPE svc_typ ENUM (T, D)
Define basics: services, service types, languages, people.
-- Service type identified by SVC_TYP_ID,
-- named SVC_TYP_NAME exists.
--
service_typ {SVC_TYP_ID::svc_typ, SVC_TYP_NAME}
PK {SVC_TYP_ID}
AK {SVC_TYP_ID}
(T, translation)
(D, dtp)
-- Service named SVC_NAME of type SVC_TYP_ID
-- is identified by SVC_ID.
--
service_ {SVC_ID, SVC_TYP_ID, SVC_NAME}
PK {SVC_ID}
AK {SVC_NAME}
SK {SVC_ID, SVC_TYP_ID}
FK {SVC_TYP_ID} REFERENCES service_typ
(WTR, T, written translation)
(EDT, T, editing)
(PRF, T, proof reading)
(IND, D, in-design markup)
(PHT, D, photoshop markup)
(ACD, D, autocad markup)
-- Language name LANG_NAME,
-- identified by LANG_ID exists.
--
lang {LANG_ID, LANG_NAME}
PK {LANG_ID}
AK {LANG_NAME}
(EN, English)
(FR, French)
(RU, Russian)
-- Perfomer (person) identified by PERF_ID
-- named FNAME, LNAME exists.
--
perfomer {PERF_ID, FNAME, LNAME}
PK {PERF_ID}
(1, Lev, Tolstoy)
(2, Jim, Blah)
(3, Joe, Doe)
(4, Jane, Doe)
People provide services, each person may provide more than one service type. In this example general term for a person providing a service is "performer". Translator and dtp-professional are subtypes of the performer supertype, descriminator is service type. It is possible for the same performer to provide more than one service type.
-- Perfomer PERF_ID registered for service type SVC_TYP_ID.
--
perf_svc_typ {PERF_ID, SVC_TYP_ID, cols_common_to_all}
PK {PERF_ID, SVC_TYP_ID}
FK1 {PERF_ID} REFERENCES perfomer
FK2 {SVC_TYP_ID} REFERENCES service_typ
(1, T, ... )
(2, T, ... )
(3, D, ... )
(4, T, ... )
(4, D, ... ) -- PERF_ID = 4 does translations and dtp
-- Performer PERF_ID is registered as a translator.
--
-- note: (SVC_TYP_ID = T)
--
translator {PERF_ID, SVC_TYP_ID, cols_specific_to_translators}
PK {PERF_ID}
CHECK (SVC_TYP_ID = T::svc_typ)
FK {PERF_ID, SVC_TYP_ID} REFERENCES perf_svc_typ
(1, T, ...)
(2, T, ...)
(4, T, ...)
-- Performer PERF_ID is registered as a DTP professional.
--
-- note: (SVC_TYP_ID = D)
--
dtp_prof {PERF_ID, SVC_TYP_ID, cols_specific_to_dtp_prof}
PK {PERF_ID}
CHECK (SVC_TYP_ID = D::svc_typ)
FK {PERF_ID, SVC_TYP_ID} REFERENCES
perf_svc_typ {PERF_ID, SVC_TYP_ID}
(3, D, ...)
(4, D, ...)
A translator may be able to provide services in more than one language.
-- Performer PERF_ID, who registered as a translator,
-- speaks language LANG_ID.
--
perf_lang {PERF_ID, LANG_ID}
PK {PERF_ID, LANG_ID}
FK1 {PERF_ID} REFERENCES translator
FK2 {LANG_ID} REFERENCES lang
(1, EN)
(1, FR)
(1, RU)
(2, EN)
(2, FR)
(4, FR)
(4, RU)
Each person (performer) may offer multiple groups of services. Each service in a group must be of the same service type. Performer must be registered as a provider of this service type.
-- Performer PERF_ID offers a group of services,
-- identified by (PERF_ID, PERF_GROUP_NO);
-- each service in the group is of type SVC_TYP_ID.
--
svc_group {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID}
PK {PERF_ID, PERF_GROUP_NO}
SK {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID}
FK {PERF_ID, SVC_TYP_ID} REFERENCES perf_svc_typ
(1, 1, T)
(1, 2, T)
(2, 1, T)
(3, 1, D)
(4, 1, T)
(4, 2, D)
Each performer's service group, lists services from that group's service-type, offered by that performer.
-- Performer PERF_ID offers translation (SVC_TYP_ID = T)
-- service SVC_ID from FROM_LANG to TO_LANG,
-- in that performer's service group
-- identified by (PERF_ID, PERF_GROUP_NO)
--
trans_svc {
PERF_ID
, PERF_GROUP_NO
, SVC_ID
, SVC_TYP_ID
, FROM_LANG
, TO_LANG
}
PK {PERF_ID, PERF_GROUP_NO, SVC_ID, FROM_LANG, TO_LANG}
CHECK (SVC_TYP_ID = T::svc_typ)
FK1 {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID} REFERENCES svc_group
FK2 {SVC_ID, SVC_TYP_ID} REFERENCES service_
FK3 {PERF_ID} REFERENCES translator
FK4 {PERF_ID, FROM_LANG} REFERENCES
perf_lang {PERF_ID, LANG_ID}
FK5 {PERF_ID, TO_LANG} REFERENCES
perf_lang {PERF_ID, LANG_ID}
(1, 1, WTR, T, EN, RU) -- for translation from <> to
(1, 1, WTR, T, FR, RU)
(1, 2, PRF, T, RU, RU) -- for edit and proof from = to
(1, 2, EDT, T, RU, RU)
(1, 2, PRF, T, EN, EN)
(2, 1, WTR, T, EN, FR)
(2, 1, WTR, T, FR, EN)
(2, 1, EDT, T, EN, EN)
(2, 1, PRF, T, EN, EN)
(2, 1, PRF, T, FR, FR)
(4, 1, WTR, T, FR, RU)
(4, 1, PRF, T, FR, FR)
-- Performer PERF_ID offers DTP (SVC_TYP_ID = D) service_ SVC_ID
-- in group identified by (PERF_ID, PERF_GROUP_NO).
--
dtp_svc {PERF_ID, PERF_GROUP_NO, SVC_ID, SVC_TYP_ID}
PK {PERF_ID, PERF_GROUP_NO, SVC_ID}
CHECK (SVC_TYP_ID = D::svc_typ)
FK1 {PERF_ID, PERF_GROUP_NO, SVC_TYP_ID} REFERENCES svc_group
FK2 {SVC_ID, SVC_TYP_ID} REFERENCES service_
FK3 {PERF_ID} REFERENCES dtp_prof
(3, 1, PHT, D)
(3, 1, ACD, D)
(4, 2, IND, D)
(4, 2, ACD, D)
