Michael Monnerie wrote:
(this mail is easier to read with a fixed charset)
Hi, I have a question to the SQL gurus on this list. I've extended the
domains table to resolve the n:m relation customer <-> domaingroups:
CREATE TABLE zmi_domains (
domain_idnr BIGSERIAL UNIQUE, --- domain id
domain VARCHAR(200) UNIQUE NOT NULL, --- name of this domain
domaingrp_idnr BIGSERIAL, --- all domains which alias to
each other are in the same group
--- if not specified,
automatically assigns next number
--- will be automatic for most
customers (only having 1 domain)
maxaccounts int8, --- maximum number of accounts
allowed
userquota int8, --- default quota per user in
this domain
domainquotasoft int8, --- quota of domain total
(soft limit, issues warnings)
domainquotahard int8, --- quota of domain total
(hard limit, bounce e-mail)
comment text,
CONSTRAINT zmidom_pk PRIMARY KEY(domain)
);
--- n:m table for customers and their domaingroups
--- a customer can have several domains which belong to an alias domain group
--- a customer can have several domain groups, if those domains do not alias to
each other
--- a customer can of course have only one domain, being alone in it's group
then
CREATE TABLE zmi_custdomains (
client_idnr INT8 NOT NULL --- customers id
REFERENCES zmi_customers(id),
domaingrp_idnr INT8 NOT NULL, --- domain group id
pridomain_idnr INT8 NOT NULL --- this is the id of the
primary domain of this group
REFERENCES zmi_domains(domain_idnr),
CONSTRAINT zmicustdom_pk PRIMARY KEY(client_idnr,domaingrp_idnr)
);
HOW can I make sure domaingrp_idnr can have only values that exist in
zmi_domains(domaingrp_idnr)? I can't find how I write a constraint
for that.
I may be missing something here, but usually if you want to have a
many-to-many relationship between two tables, you use a link table, so
between zmi_domains and zmi_customers you would have a third table,
something like:
zmi_cust_domains_lookup(
id serial primary key,
domain_idnr bigint references zmi_domains.domain_idnr,
client_idnr bigint references zmi_custdomains
)
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
