One-to-one relationship

[lachesis]

I've got two tables:
t_ORDER
t_QUOTE

t_QUOTE generates a quote number each time we make contact with a client - our products are customised for each client.

However the QUOTE will not always materialise into an ORDER (purchase order). About 50-60% of QUOTES turn into ORDERS.

My thoughts were I should have two separate tables, and relate them both in a one-to-one relationship. But I now think I should have a third table (foreign key table) with relationships to both t_ORDER and t_QUOTE.

I do not want to create a new ORDER number for a QUOTE that dies ie. no purchase order number from the Customer.

Is this the tidiest way to do this?

Should it look something like:

t_QUOTE
one
|
many
t_QUOTE_ORDER
many
|
one
t_ORDER

Any thoughts/help appreciated??

 

[CsaintAZ]

lachesis,

It depends on what you want to do with the data. A one-to- many relationship from the Quote file to the Order file will result in a list of all quotes where an order also exists. If an order does not exist, the Quote will still appear in your report, query, etc. If there might be a case where an Order exists without a quote, then you would probably want a one-to-many from the Order file to the Quote file. Having a one-to-one relationship between the Quote an Order file will result in queries, reports that only show quotes where an order also exists. Qoutes with no orders, and orders without quotes would not be listed when viewing or reporting on this relationship.

 

[lachesis]

I dont believe that is so. I could use a left outer join, or a right outer join where I wanted to include the details from one table, even if the matching data in the other table is blank.

Here are some typical scenarios:

SCENARIO 1

1. A customer requires a quote.

2. The quote is generated at by the supplier at detail level: item, quantity, cost per item, incidentals

3. The quote is sent to the Customer, and rejected.

4. The supplier uses the same quotation, but substitutes some items for others.

5. The quote is re-submitted to the Customer.

6. The Customer accepts the quote but requests delivery a nonth out. No purchase order is provided at this stage.

7. Two weeks later the Customer supplies the Purchase Order number.

8. The Order is processed, within the delivery window.

SCENARIO 2

Steps 1 thru 5 as above.

6. The Customer rejects the quote again, and terminates further transactions.

7. The quote is dead. No order is received.


SCENARIO 3

1. The customer orders without a quotation.

2. The Purchase order is placed straight-away.


In scenario 1, there would be 1 record in t_QUOTE and 1 record in t_ORDER ie. one to one.

In scenario 2 there would be 1 record in t_QUOTE and no matching record in t_ORDER ie. one to none.

In scenario 3, there would be 1 record in t_ORDER and no matching record in t_QUOTE ie. one to none.


I'm not convinced anything other than a one-to-one relationship will work with this one.

 

[KenReay]

Hi

Two table should suffice

tblQuotes

QuoteNo
ClientId
...etc

tblOrders
OrderNo
QuoteNo
ClientONo
...etc



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lachesis]

Thanks Ken, that's what I figured.

But how would I enforce referential integrity and a one-to-one relationship on tblQuotes.QuoteNo to tblOrders.QuoteNo?? That's where i get confused...

 

[SkipVought]

lachesis,

You can have...

Quotes without Orders

Orders without Quotes

Quotes with Orders (one to one)

No way to enforce referential entegrity, is there?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lachesis]

Yes those conditions can all exist.

But the rules for referential integrity (according to Access Help: What is Referential Integrity?) are all met. This is why I'm assuming I can enforce it.

 

[KenReay]

Hi

According to my reading of your initial post you specifically say that cannot have an order without a quote, so I can see no problem with the two table approach and a relationship between Orders.QuoteNo -> Quote.QuoteNo.
Have I miss read your initial post or have the rules been changed along the way?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[CsaintAZ]

lachesis,

Sorry for the confusion. In my original response, replace the first one-to-many with one-to-one.

The rest of the reply still stands. A one-to-many relationship from the Quote file to the Sales Order file where I am assuming the quote is unique, is a left-outer join. If you setup this relationship in Access or some other DBMS, run a query on the two tables and review the SQL statement that results. (Quotes LO Orders)

A third table would only be required if you had a many-to-many scenario. Table3 would have the quoteID and the orderID as fields. The quote file would link to table3 in a one-to-many by the quoteID. The Order file would link to table3 in a one-to-many by the orderID. This would only be necessary if multiple quotes could relate to an order. Possible I guess, but not likely.

 

[CsaintAZ]

lachesis,

Me again. I just re-read your second post and caught the bit about an order with no quote. In that case you would need the third table mentioned above.

 

[RoyVidar]

Hi!

Just my couple of cents, and perhaps an alternative...

I would refer to this quote - order challenge, according to your second post, and also SkipVought's observation, as a "symmetric" 1:1 relationship, which I'd represent in a conseptual model, including both both minima and maxima cardinality like this:

Quote 1-0 ---------- 0-1 Order

One quote might or might not relate to an order, but if related, then to only one order, and vice versa. Consequence of this, no matter which entity type's PK is considered worthy of being the FK in the other entity type, would have N entries on the FK side with NULL. Although it's possible in Access, and probably other RDBMS too, to create such relationships, and in Access check "Enforce Referential Integrity", I'm not sure that what's being enforced is what one wants to enforce. I'm not very happy using NULL as possible FK value.

From reading this thread, it seems only 1:M, 1:N is presented for “breaking up” symmetric 1:1 relationships.

Might this structure do?

Quote 1-1 ---------- 0-1 Quote-Order 1-0 ------------ 1-1 Order

Since (what I like to refer to as) non symmetric 1:1 relationships are somewhat common and solveable, why not split the symmetric 1:1 relationship into two non symmetric 1:1 relationships, having the third entity type Quote-Order representing this.

PK of Quote would become FK of Quote-Order
PK of Order would become FK of Quote-Order
For Quote-Order, the combination of the two FK's might be used as the entity type's PK (implementet with "No dupes&quot, and would, in my view, be able to do some "enforcement", and providing no FK NULL;-)

To me, it would be either that, or consider using one entity type.
In your second post, I get a rather strong feeling the only thing separating an entity (instance) of quote from an entity of order, is the fact that the order gets a purchase order number. Arguments here, could be that, having or not having an purchase order number, separates the quote and the order. The quote is just an order “waiting to happen”, a status of an order that might or might not become a "purchable" order. I think this might be some of the arguments for using one entity type.

HTH Roy-Vidar