We have something similiar ...

We have a PEOPLE table with all the people that the application
interfaces with. This table has columns like:

peopid (Primary Key)
name
address1
address2
city,
etc.

Then we have subsidiary tables like USERS that has two fields in it:

peopid (foreign key to people)
loginkey

and a table PROPOWNERS that has a few fields like

peopid (foreign key to people)
propid
ownertype

This works in that if I want to find all owners I don't have to look at
all people. I can also have some 'specific columns' for specific
reasons the subsidiary tables and they don't have to be repeated for all
people. It works if you have one to many issues like our PROPOWNERS
where one person can own many properties.

Works for us.

Todd


Rick Roen wrote:

>I am in the process of redisigning a FB DB. I read a bit about
>using a Unique Object identifier (OID's) for all sequential numbers
>used in tables like Customer numbers, invoice numbers etc.
>
>This got me thinking about converting all my businss contact into
>just one table. Currently I have Customers, Vendors, Employees,
>Billto and Shipto tables all with the same basic fields. I was
>thinking about adding them all into one big table and add
>some "boolean" fields (0/1) to identify the records type - which
>could be customer/vendor/employee etc. The same contact could belong
>to multiple categories.
>
>Then when I wanted to display a customer and billto I would do a
>self-referencing (or maybe re-entrant) query like:
>
>Select C1.ContactNum, C1.ContactName, C1.Address1, C1.Address2,
>C1.City, C1.State, C2.ContactName, C2...
>>From CONTACTS C1
>Left Join CONTACTS C2 on C1.BILLTO = C2.ContactNum
>
>This business has quite a few customers who are also vendors and
>might occasionally sell to an employee so there is some overlap in
>categories.
>
>Does anyone has some opinions about if this is a viable option for
>design?
>
>Rick
>
>
>
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>