newbie question: relation vs. reference

[cjlesh]

Howdy:

I've been messing around with Filemaker, working on a project to track the sale of gift cards.

My five tables (so far):/

Customers (customerId <-PK, customerFirstName, customerLastName, customerAddress1, customerAddress2, customerCity, customerState, customerZipCode, customerPhone, customerEmailAddress)

Vendors (vendorId <-PK, vendorName, vendorAddress1, vendorAddress2, vendorCity, vendorState, vendorZipCode, vendorPhone, vendorEmailAddress)

Cards (cardId <-PK, vendorId <-FK, amount, discount)

Orders (orderId <-PK, date, customerId <-FK)

OrderLineItems (orderLineItemId <-PK, orderId <-FK, cardId <-FK)

In the above tables I am using relationships to avoid duplicating data, like I think I should according to my extremely limited knowledge of databases. Every table has a (meaningless) primary key, and foreign keys are used wherever I am sharing data.

However, I can't seem to understand how to construct data entry layouts that are user friendly. For example, I have a layout for adding or viewing orders, with a portal to OrderLineItems, but what is the best way to select the gift card I want to add to the order, of the actual data being entered is a meaningless cardId?

So I thought that instead of using a relation between the "Cards" table and the "OrderLineItems" table, I would simply use the vendors and amounts as references for drop down lists in the OrderLineItems entry.

This has two benefits that I can see:

1) Much more natural data entry

2) If a card changes in the future (a vendor stops carring a certain amount, or the discount changes) then the previously entered orders will not change, which is good.

On the flip side, I could add an "active" field to the "cards" table, and only allow OrderLineItems to include currently active cards, which is good so that I can look back and see what used to be offered by a certain vendor, etc...

I guess my question is, when should I use a relation between tables, and when should I just use a table as a value list for another table?

Any pointers in the right direction would be helpful,

CJL

[Tim W]

Hi,

When you setup the value list from the other table fields, at the bottom of the dialogue you can select "show values only from the second field". This will present only the second field (Desc or whatever), not the first field (your key), to the user, but behind the scenes, insert the key into the field in reality. Upon each view, the meaningful second field will be presented.

HTH,

Tim

[cjlesh]

Thanks for the tip.

This still doesn't solve my problem using the purely relational solution. How do I set up an order entry layout that has meaning to the user? A portal the the orderLineItems let me add lineitems by cardId, which by itself has no meaning. I would like to be able to add a line item by vendor and amount, but this would involve some type of trick that I am not abole to figure out, which is why I started down the path of using reference instead of relationship, and actually storing the vendor and amount in the lineitem table.

Any other thoughts on this problem? I'm guessing this is a common problem, but in my very limited reading I have not come up with a decent solution.

Thanks again,

CJL

[bruceR]

It is pretty common to have a "product picker" feature, either as a second portal on the layout, or in another window. The product picker portal would be filtered by product type or some other criteria, apparently vendor and price range in your case. Then you have a button in the product picker portal that adds the item to the line items portal. Frequently people like to turn OFF the "automatically create new record" feature in the line items portal so that some kind of scripted creation of new records must be performed.

[cjlesh]

Bruce:

Thank you for your reply.

What you are suggesting makes perfect sense, and must be obvious to experienced developers.

I will give it a try.

-CJL