[AccessD] A2003: Design Question

Darren DICK wrote:

>Hi Stuart
>Thanks for the reply
>In your suggestion I would create a 3rd table called tblEntity - yes?
>How does it get populated?
>
>How would I store entries to the tblTimeBilling table.
>IE in the union combo how would I store that PK 4 in this case is a
>franchise
>Not a client?
>
>Many thanks
>
>Darren
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
>Sent: Tuesday, 16 November 2004 12:25 PM
>To: Access Developers discussion and problemsolving
>Subject: Re: [AccessD] A2003: Design Question
>
>On 16 Nov 2004 at 12:03, Darren DICK wrote:
>
>  
>
>> 
>>BUT my client does work for both the Franchises and individual Customers. 
>>In order to present both of them in a TimeBilling Screen I use a union 
>>query to 'merge' both groups into a single combo
>> 
>>The problem is storing the PK correctly. eg say I select the 4th 
>>Franchise in the list - it has a PK of 4 Well an entry on the client 
>>side will also have a PK of 4.
>> 
>>I am tossing up with various ways to deal with this including merging 
>>both tables into one table and just setting a flag to differentiate 
>>Clients from Franchises.
>>Of course I can have separate fields in the tblTimeBilling for 
>>FranchiseID and for ClientID.
>> 
>>    
>>
>
>I'd probably go for a tblEntity with 3 fields:
>
>ID
>EntityType (Franchise or Client)
>EntityID
>
>You can then base your ComboBox on a query something like:
>
>Select ID , IIF(EntityType = "F", FranchiseName,ClientName) as EntityName
>>From tblEntities Left  Join tblFranchises ....... Left Join TblClients
> ......
>
>
>
>
>--
>Stuart
>
>
>  
>
Is there a profitable reason to store these entities in different 
tables? There may well be, but in your problem description it is not 
apparent. If my hunch is correct, then you can put both Franchises and 
Customers in the same table, adding a flag to indicate which. There is 
the possibility however that an entity is both a Customer and a 
Franchise, in which case a boolean won't do, so replace it with a numeric.

Up side: much simpler combo selectors.
Down side: selecting only one or the other type demands hitting the 
whole table (unless the BE is SQL).

HTH,
Arthur