>Up to now we have kept different companies' data in different directories, so there hasn't been a question of which company owns a piece of data. We are now creating an add-on product that keeps its data in sql server and plan to keep several companies in the same database with a Companies table that assigns a CompanyID for each company.
>
>I wonder which other tables should have a CompanyID column. It seems enough to indicate companyID in the top table of a group of tables that are related as parent/child/grandchild. Or should each record of each table indicate to which company it belongs? The second alternative opens up the possibility of conflicting CompanyIDs being stored in parent, child and grandchiled records.
>
>I'd appreciate suggestions.
>
>TIA,
>
>Alex
You only need to store CompanyID in the Companies (or Clients) table. The tables that refer to that Companies table directly through the FK relationship will have the CompanyID column. If the table is not directly related with the Companies table, it should not have that column.
If it's not broken, fix it until it is.
My Blog