create table addrs (
    addrid int identity(1,1) primary key,
    street1 varchar(50) not null,
    street2 varchar(50) not null,
    city varchar(30) not null,
    zip char(9) not null )

create table users (
    login varchar(12) not null primary key )
create table companies (
    coname varchar(25) not null primary key )

create table addrbook (
    addrid int not null primary key
        constraint fk_addrbook_addrid references addr(addrid),
    login varchar (12) null
        constraint fk_addrbook_login references users(login),
    coname varchar(25) null
        constraint fk_addrbook_coname references companies(coname),
    constraint ck_addrbook_login_coname check
        ((login is null and coname is not null) or
         (login is not null and coname is null))

create table addrbook (
    addrid int not null primary key
        constraint fk_addrbook_addrid references addr(addrid),
    login varchar (12) null
        constraint fk_addrbook_login references users(login),
    coname varchar(25) null
        constraint fk_addrbook_coname references companies(coname),
    constraint ck_addrbook_login_coname check
        ((login is null and coname is not null) or
         (login is not null and coname is null))

quote:

---

I have a question: what's to prevent two different companies from having the exact same address? Not just street address, but actual floor/room/suite # too. I've yet to see two companies working in the exact same office space. It's just a detail, but you might want to consider it.

---

quote:

---

I suppose you are right, but I can't make a composite key cause either addrbook.login or addrbook.coname must be null (it either a companies address or a users address).

---

create table addrbook (
    addrid int not null 
        constraint fk_addrbook_addrid references addr(addrid),
    name varchar (25) null
        constraint fk_addrbook_login references users(name)
    constraint pk_addrbook PRIMARY KEY (addrid, name) )

quote:

---

If I were to tell you that there was all manner of supporting data for each of these entities which isn't shared, would that change your position?

---

Nope, I would have the same position as I usually state to newbie questions:

POST ALL OF YOUR ACTUAL TABLE STRUCTURES!

You're not playing fair in leaving out the important stuff!

quote:

---

If I were to tell you that not every user is associated with a company (in fact only a small % of users have company data), would that change your position?

---

No. You could add a UserCompanies table with 2 columns, one for the person's ID and one for the company's ID. Assuming you don't already have a table like this...you DIDN'T POST YOUR DDL!

quote:

---

So my Departments table would reference users(name)? So my UserRoles table would reference users(name)?

---

You could do something similar to my earlier post: roll both UserRoles and Departments into a combined table and use an IsDepartment flag, etc., or add a "Type" char/varchar column indicating "role" or "department" or any other kind of attribute for the column. I think that might still work. The primary key could remain the single "name" column, or use the name and type column pairs, depending on whether a person/company can be both types.

quote:

---

How would make people users not have departments and company users not have roles?

---

Uhhhh, English?

If you use the two-column combined RolesDepartments structure, you can simply add a row for every role/department a person/company can have. Of course, SINCE I DON'T HAVE YOUR EXACT DDL...I'm LOVING the irony here...

quote:

---

I was just going to with a UsersAddressBook and CompanyAddressBook with composite keys on AddrID and {Login,CoName}(respectively). Sure, I'll probably have to deal with preventing a dupe between a persons work addr and a companies addr for the company that person is related too, but I can do that in the proc.

---

Nothing wrong with this approach either; as long as it works it's the right way to do it.

I was thinking that since you love to dive deep into esoteric design issues I might offer up an alternative that uses natural keys

create table UsersRolesDepartmentsSubscriptionsPersonnelPrivsAuthenticationFailures (
    username varchar(25) not null,
    relationshiptype varchar(20) not null constraint fk
        references relationships(relationshiptype),
    deptname varchar(25) null,
    subscription varchar(20) null,
    coname varchar(25) null,
    priv varchar(50) null,
    logonfailures tinyint null,
    firstfailure smalldatetime null
    constraint pk_IDontWantToTypeThisLongName Primary Key(username,relationshiptype))

create table relationships (
    relationshiptype varchar(20) not null primary key )

quote:

---

quote:

---

How would make people users not have departments and company users not have roles?

---

Uhhhh, English?

---

quote:

---

My "Uhhhh? English?" comments relates to the part in red: it's got some grammatical errors.

---

Sorry, I missed your meaning even on the second read!!! I wish English had a SET PARSEONLY ON option .....I can't type/spell for shyt...you should no that bye now....BWAHAHAHA

<O>