How to create dynamic parameters in SQL Server stored procedure

Groaner

Null has no place in a database!

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?

create table persons
(
   person_id int identity(1,1),
   title varchar(30),
   first_name varchar(50),
   middle_name varchar(50),
   last_name varchar(100),
   suffix varchar(30),
   birthdate datetime,
   constraint pk_persons primary key (person_id)
)

create table addresses
(
   address_id int identity(1,1),
   addressees varchar(100),
   address_line1 varchar(100),
   address_line2 varchar(100),
   address_line3 varchar(100),
   city varchar(100),
   county varchar(100),
   state_province varchar(100),
   postal_code varchar(20),
   country varchar(100),
   constraint pk_addresses primary key (address_id)
)

create table person_addresses
(
    person_address_id int identity(1,1),
    person_id int not null,
    address_id int not null,
    is_primary bit not null,
    single_primary_address as case when is_primary = 1 then null else person_address_id end,
    constraint pk_person_addresses primary key (person_address_id),
    constraint uq_person_addresses unique (person_id, address_id),
    constraint uq_person_addresses_single_primary_address as (person_id, single_primary_address),
    constraint fk_person_addresses_persons foreign key (person_id) references persons,
    constraint fk_person_addresses_addresses foreign key (address_id) references addresses
)

I'm all for normalization where it adds value, but my address book query would only involve three tables.

@dkf said:

I take it you've never encountered someone genuinely without a surname. I have.

Names and addresses are a world of hurt.

Matches

You forgot person_id in the addresses table.

Also, where are the FK constraints?

Amateur.

boomzilla

@Matches said:

Also, where are the FK constraints?

Enforced in the app. Duh.

chubertdev

@Matches said:

You forgot person_id in the addresses table.

Also, where are the FK constraints?

Amateur.

t_FK_Constrains_bak_20110427

Groaner

@Matches said:

Also, where are the FK constraints?

Edited because you asked so nicely (and I forgot).

@Matches said:

You forgot person_id in the addresses table.

No I didn't. Scroll down for person_addresses. It's a many-to-many relationship.

redwizard

@M_Adams said:

Really, you're going to stuff a null into a bit field‽

As a matter of fact, YES.

darkmatter

@redwizard said:

As a matter of fact, NULL.

NTFY

Groaner

@darkmatter said:

redwizard said:
[i]NULL[/i]

NTFY

CNYNTFY

ben_lubar

@darkmatter said:

Easy, a bit field where a 0 means "NO", a 1 means "YES". A NULL means not answered.A space isn't even a valid entry here.... Or do we have to redesign all our database schemas to work without NULL support?

Don't let Oracle know. They'll make 0 ≝ NULL for integer fields.

dkf

@Groaner said:

Names and addresses are a world of hurt.

Addresses are easy. Don't parse them unless you must.

PJH

@Groaner said:

Scroll down for person_addresses

I've stopped code blocks getting scrollbars (I may re-introduce a (larger) limit but we'll see how it goes for the moment). I recall being asked about that last week, but was only on mobile phone so couldn't do anything at the time.

tarunik

@dkf said:

Groaner said:
Names and addresses are a world of hurt.

Addresses are easy. Don't parse them unless you must.

At least Midlake, UT is no longer a going concern...try to imagine getting mail, parcels, etal to someone who can't be reached by road in this day and age! (History note: Midlake was a very tiny RR town built out in the middle of the Great Salt Lake, along the Lucin Cutoff. There were no roads to speak of there, just a railroad track substituting for Main Street; everything came in and out on a train.)