Continuing the discussion from [How to create dynamic parameters in SQL Server stored procedure]


  • BINNED

    @Groaner said:

    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 )
        )
    

  • Discourse touched me in a no-no place

    What about people with two middle names? :trollface:



  • 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.

    @M_Adams said:

    email_address text not null, -- validation left as an exercise

    Best bet would be to try sending to it.

    @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?


  • BINNED

    @Groaner said:

    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).

    @Groaner said:

    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.

    @Groaner said:

    @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 Prince's Logo? Sometimes you just need to tell people to stop their assholery...


  • ♿ (Parody)

    @M_Adams said:

    Sometimes you just need to tell people to stop their assholery...

    Stop this normalization assholery, please.



  • @M_Adams said:

    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.


  • BINNED

    @boomzilla said:

    Stop this normalization assholery, please.

    Shiiiit, set myself up for that one, I did! :)


Log in to reply