Very Computer

If the second were the preferred method, then it might look like:

Customer       Vendor         Address
--------------        ----------------    ---------------
Cust_id           Vend_id      Addr_id
                                          Parent_id FK
...                     ...                ...

I am using identity columns as the primary key. I would then need to
insure that Parent_id was unique among the Customer and Vendor tables
(each of their identities must be unique). This sounds too
complicated. Help. What is the proper way to do this?

I have both my customer and vendor info in one table with a column that
identifies the record as Customer or Vendor. Works well for my but my cust
or vendor info requirements are the same. I see nothing wrong with having a
address table as being separate either( having 3 tables cust, vendor,
address). Actually I've seen that done with a couple of accounting apps.

Both work well.

I guess it really depends on how many records your gonna have.

GDP

Quote:> I have two tables: Customer and Vendor, both of which need to contain
> address information. Assuming the Address info contains Address-1,
> City, State, Zip, is it better to have these fields defined in
> Customer and Vendor, or should Address be a separate table, with a
> one-to-one relationship to each of the Customer and Vendor tables?

> If the second were the preferred method, then it might look like:

> Customer       Vendor         Address
> --------------        ----------------    ---------------
> Cust_id           Vend_id      Addr_id
>                                           Parent_id FK
> ...                     ...                ...

> I am using identity columns as the primary key. I would then need to
> insure that Parent_id was unique among the Customer and Vendor tables
> (each of their identities must be unique). This sounds too
> complicated. Help. What is the proper way to do this?

On second thought, If your customer can also be a vendor (or vice versa) I
would lean towards not having an address table. I think it would save some
time on client development. Either put customer or vendor data together (if
you want a unique id across both cust an vendor), one table for each
customer and vendor.

HTH

GDP

Quote:> I have two tables: Customer and Vendor, both of which need to contain
> address information. Assuming the Address info contains Address-1,
> City, State, Zip, is it better to have these fields defined in
> Customer and Vendor, or should Address be a separate table, with a
> one-to-one relationship to each of the Customer and Vendor tables?

> If the second were the preferred method, then it might look like:

> Customer       Vendor         Address
> --------------        ----------------    ---------------
> Cust_id           Vend_id      Addr_id
>                                           Parent_id FK
> ...                     ...                ...

> I am using identity columns as the primary key. I would then need to
> insure that Parent_id was unique among the Customer and Vendor tables
> (each of their identities must be unique). This sounds too
> complicated. Help. What is the proper way to do this?

I must have Customer and Vendor in separate tables. So if I have a
separate Address table, I should use uniqueidentifier (GUID) as the
data type?

Thanks - Larry

On Thu, 11 Jul 2002 10:52:33 -0400, "Gmann"

Quote:>GDP,

>I must have Customer and Vendor in separate tables. So if I have a
>separate Address table, I should use uniqueidentifier (GUID) as the
>data type?

From a database point of view I probably would do with and Customer
and an Vendor table and not use an address table. Except if you have
a reason to go with the address table. The amount of redundance is
very limited because most customers are not vendors.

From an OO point of view, you could reason that the address is common
to both vendor and address, that you have functionality on the address
and do not want to build that functionality twice so create a
sepperate address table. (First offcourse going through class design
etc. and then implementing the class design).
Generate an identity in the address table and use this as join field
or you could even use this as a referential constraint field. An
address can be from more than one customer/vendor if you want this,
but you this doesn't need to be.

From a performance point of view, this could work both ways. If you
use the tables a lot without an huge address, leaving out the address
might give an performance advantage. If you always use the address or
part of the address, keeping it in the tables might lead to a
performance advantage. (Probably performance is not an issue here).

From the application point of view, this you have to fill in yourself.

Conclusion :
If you do not have sepperate functionality on a address store it
within the customer and vendor tables. Even if you need functionality
on addres you probably could make a view where you can select the
addressfields of both tables and do a union on that.

ben brugman

Ben Brugman