Multiple lookups to same table - is table structure flawed/un-normalized???

[jason]

I am on the verge of completing a critical boat listing database for my
company with ancillary queries and forms and eventual streaming to the web
via asp.

I am concerned that my primary table - tableListings - is not normalized
with the fk fields mentioned below. The boat goes through various stages
until it is sold and I have designed the database to allow the administrator
to flag which stage the boat is in via the field Market_Status ID (looks up
tableMarketStatus).

The key stages are:

- Available
- Under Offer
- Under Contract
- Acceptance
- Sold

There is always ONE selling Broker and sometines a Listing Broker. If the
vessel is in the second stage - Under Offer - then there may be temporarily
an Under_Offer Broker. All the broker fields in the tableListings lookup
the Broker Name from: tableBroker. Finally, we have to assign what
commission the broker is getting. I elected to do all of the above in a
horizontal styel in the the tableListings. This makes it easier for me to
generate queries and keep things in one place. But, I fear I am making a
mistake......

Is the following detrimentally flawed. Is the fact that a number of my key
Broker_ID fields are referencing the same lookup table - tableBroker - a
problem. I have noticed that my queries break down in design view if I
attempt to bring the lookup table into the same pane as the tableListings
table due to the multiple looksups.\

I really need some specific help in pinpointing problems...or...can I get
away with this? APPRECIATED!! - Jason

-- tableListing --
ListingID (1)
Listing (eg: a Catamaran called 'Ocean Speed')
Market_Status_ID (looks up: tableMarketStatus [Available, Under Offer, Under
Contract, Acceptance, Sold])
Listing_Broker_ID (looks up: tableBroker [None, sw, jw, td, jb, sd])
Listing_Broker_Commission_ID (looks up: tableCommission [None, 10%, 15%,
35%, 45%, 55%])
Selling_Broker_ID (looks up: tableMarketStatus [Available, Under Offer,
Under Contract, Acceptance, Sold])
Selling_Broker_Commision_ID tableCommission [None, 10%, 15%, 35%, 45%, 55%])
Under_Offer_Broker_ID (looks up: tableMarketStatus [Available, Under Offer,
Under Contract, Acceptance, Sold])
Under_Offer_Broker_Commission_ID tableCommission [None, 10%, 15%, 35%, 45%,
55%])

 

[John Vinson]

The key stages are:

- Available
- Under Offer
- Under Contract
- Acceptance
- Sold

There is always ONE selling Broker and sometines a Listing Broker. If the
vessel is in the second stage - Under Offer - then there may be temporarily
an Under_Offer Broker. All the broker fields in the tableListings lookup
the Broker Name from: tableBroker. Finally, we have to assign what
commission the broker is getting. I elected to do all of the above in a
horizontal styel in the the tableListings. This makes it easier for me to
generate queries and keep things in one place. But, I fear I am making a
mistake......

Well, this is a judgement call. I would probably have split this out
into a many to many relationship between your Listings table and a
(five-row, at least at present) Stage table. You might even want the
flexibility to have a property listed with more than one broker (I
don't know your business but I did sell my house once... :-{) )

Either way can be made to work, and having five separate relationships
from your Listing table to the Broker table is not, by itself, a
problem.

I would suggest, however, that you NOT use the "table lookup" field
type AT ALL. It does more to confuse matters than to help. You can use
Lookups (combo boxes) on a Form, with five combo boxes using
tableBroker as RowSource and bound to these five fields, without
having to use the blinking Lookup Wizard.

 

[jason]

Hey John,

I had a feeling you would sugges the many-to-many resolution to enable
normalization bwt Listings & Brokers; Listings & Stages

But, the only problem with this approach is that it leads to complicated
queries when you need to extract eg: One listing and all associated Brokers
.....

as.....the multiple instances of the listing in eg: qry_Listings_Brokers
....will create multiple records whichs screws up display on the website...

How do you normally handle this - and - just to confirm are you saying I can
get away with my 'horizontal' unnormalized structure at present in the
primary Listings table?

- Jason

 

[John Vinson]

Hey John,

I had a feeling you would sugges the many-to-many resolution to enable
normalization bwt Listings & Brokers; Listings & Stages

But, the only problem with this approach is that it leads to complicated
queries when you need to extract eg: One listing and all associated Brokers
....

Well, not that hard at all. Join the Listings table to the
Listings_Brokers table to the Brokers table.

as.....the multiple instances of the listing in eg: qry_Listings_Brokers
...will create multiple records whichs screws up display on the website...

For *display* purposes (not for updating) you could use a Crosstab
(not updateabl of course).

How do you normally handle this - and - just to confirm are you saying I can
get away with my 'horizontal' unnormalized structure at present in the
primary Listings table?

If the list of stages will never change, and you're willing to accept
the restriction that one and only one broker can be involved at each
stage (which may be a more serious limitation) I think it would be ok
under the guise of "judicious denormalization, only when necessary".