Continuing the discussion from [How to create dynamic parameters in SQL Server stored procedure]
-
Assuming that, what's the minimum amount of tables that it would take to implement the following schema? How small or large a query would it be to generate, say, an address book from this?
I did want to respond to the OP, sooner, but with month end and reconciliations/financials due... I just couldn't convince myself to put the time in to respond with a "[spoiler]full retard[/spoiler]" normalization. In the original set of tables, I couldn't figure out the intended usage / usefulness of addresses.addressees so I dropped it.
I'm not going to "defend" this particular decomposition ( I may explain certain choices). The way I'd actually normalize a set of tables is intimately intertwined with it's supposed use case. Some of the tables below would be unnecessary if DBMS products actually supported generic database level ASSERTIONs, along with subselects inside CHECKs. Most don't and those that do, have various caveats and restrictions that make them less than useful.
In so far as the "size" of the query to generate an address book from these (or even the originally posted) tables... That is entirely dependant on what (in your view) constitutes an address book, and I have no desire to enter into a "measuring contest" per se.
I decided to go with Postgresql's dialect ( it's marginally less typing ). text is a nice type in PG, you only pay for what you use, storage wise.
So without further ado, I present a possible "[spoiler]full retard[/spoiler]" normalization. Enjoy.
-- people: create table persons ( person_id serial primary key, first_name text not null, last_name text not null, birth_date date not null ) create table person_titles ( person_id int foreign key references( persons.person_id ), title text not null, primary key ( person_id, title ) ) create table person_suffixes ( person_id int foreign key references( persons.person_id ), suffix text not null, primary key ( person_id, suffix ) ) create table person_middle_name ( person_id int foreign key references( persons.person_id ), middle_name text not null, primary key ( person_id, middle_name ) ) -- phone numbers: create table phone_nbr_types ( phone_code text primary key -- ex: 'fax', 'work', 'cell', 'home' ) create table phone_numbers ( person_id int foreign key references( persons.person_id ), phone_nbr text not null, -- validating this is left as an exercise phone_code text foreign key references( phone_nbr_types.phone_code), primary key ( person_id, phone_nbr ) ) create table person_default_phone ( person_id int, phone_nbr text, foreign key ( person_id, phone_nbr ) references phone_numbers, primary key ( person_id ) ) -- email: create table email_addresses ( person_id int foreign key references ( persons.person_id ), email_address text not null, -- validation left as an exercise email_code text foreign key references ( phone_nbr_types.phone_code ), -- above reference notation is not a typo: my cell email <> home email for example primary key ( person_id, email_address ) ) create table person_default_email ( person_id int, email_address int, foreign key ( person_id, email_address ) references email_addresses, primary key ( person_id ) ) -- addresses: create table addresses ( -- may need more normalization/editing -- if some of these aren't applicable -- actual validation of an address is left as an exercise -- may wish to have an "address_type" ie "Remit", "Bill To", "Orders To", etc -- see table phone_nbr_types for example. address_id serial primary key, first_address_ln text not null, -- must have *at least one* address line city text not null, county text not null, -- or parish/province/canton/arrondissement etc. state text not null, -- or territory/Bezirke/department etc. postal_cd text not null, country text not null, primary key ( address_id ) ) create table more_address_lines ( address_id int foreign key references( addresses.address_id ), line_nbr int not null check ( line_nbr > 1 ), -- it's your job to make sure the lines remain "in order" -- this table is to hold the conceptual 2nd and additional "address lines" line_txt text not null, primary key ( address_id, line_nbr ) ) create table person_addresses ( person_id int foreign key references( persons.person_id ), address_id int foreign key references( addresses.address_id ), primary key ( person_id, address_id ), ) create table person_default_addr ( person_id int, address_id int, foreign key ( person_id, address_id ) references person_addresses, primary key ( person_id ) )
-
What about people with two middle names?
-
Addressees refers to the names of recipients above the first line of the address. Some people might prefer "Mr. John Smith & Mrs. Jane Smith," others might prefer "Dr. and Mrs. Arnold Jones."
An "address book" would list people, addresses and their contact information. Since people can live in more than one place, and multiple people could live in the same place, it could be either person-driven or address-driven (and in some sensible alphabetical order).
Interesting that you chose to have separate tables to indicate default contact information - I haven't seen that pattern before as a way to enforce the "you can have infinitely many of this type, but only one of this type" requirement.
email_address text not null, -- validation left as an exercise
Best bet would be to try sending to it.
What about people with two middle names?
Separate them with a space. We're never going to need to parse full names anyway, right Mary Jane and John Paul?
-
Addressees refers to the names of recipients above the first line of the address. Some people might prefer "Mr. John Smith & Mrs. Jane Smith," others might prefer "Dr. and Mrs. Arnold Jones."
Gotcha, sounds legit to me. Then I'd say given this particular schema, you'd put that info into addresses.first_addrdess_ln and enforce that at least one additional address line be supplied. Or you could stick a "preferred_salutation" attribute into the table ("preferred_salutation" being, I think more generally understandable than "addressees", YMMV).
I haven't seen that pattern before as a way to enforce the "you can have infinitely many of this type, but only one of this type"
It's one of those decompositions that wouldn't be necessary if subselects in CHECK, or database-wide ASSERTIONS were generally available and useful. It's a form of "horizontal decomposition". Elimination of nulls in a schema involves, first "vertical decomposition" — carve off all nullable attributes into their own tables, then "horizontal decomposition" — separate null (α) tuples from non-null (β) tuples into their own tables and assert that α ∩ β = ∅ . See: SQL and Relational Theory, 2 Ed: Appendix C - A Relational Approach to Missing Information.@dkf said:
What about people with two middle names?
Separate them with a space. We're never going to need to parse full names anyway, right Mary Jane and John Paul?What about people who are as goofy as ? Sometimes you just need to tell people to stop their assholery...
-
Sometimes you just need to tell people to stop their assholery...
Stop this normalization assholery, please.
-
What about people who are as goofy as ?
Ignore it1. I refuse to accept that
anyoneanything that stupid could live.1 Apparently Discourse agrees with me, since it won't quote the symbol.
-
Stop this normalization assholery, please.
Shiiiit, set myself up for that one, I did! :)