How to create dynamic parameters in SQL Server stored procedure
-
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.
I take it you've never encountered someone genuinely without a surname. I have.
Names and addresses are a world of hurt.
-
You forgot person_id in the addresses table.
Also, where are the FK constraints?
Amateur.
-
-
You forgot person_id in the addresses table.
Also, where are the FK constraints?
Amateur.
t_FK_Constrains_bak_20110427
-
Also, where are the FK constraints?
Edited because you asked so nicely (and I forgot).
You forgot person_id in the addresses table.
No I didn't. Scroll down for
person_addresses
. It's a many-to-many relationship.
-
-
-
-
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.
-
Names and addresses are a world of hurt.
Addresses are easy. Don't parse them unless you must.
-
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.
-
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.)