Needs conflicting - relational database boggler

[keshalyi]

OK, before I get too far, let me describe to you the basic system I'm trying to beat out here...

My company owns a number of weekly shopper newspapers, and I'm revamping the database they use to store and output their classified ads for these papers. Here's the problem

On the one end, we have the Ads database, the main core. Now, each Ad can have multiple Publication Dates, and multiple publications - that is, it can be printed in three subsequent weeks, in three seperate papers, each week. At the current time, every publication date denotes a printing in every selected publication on that date - that is, they cannot print the ad for one week in one paper and three weeks in another - but this may change in the future.

Be that as it may, to summarise, there is a many to many rel between publications and ads and publication dates and ads. And then, there is a many to many relationship between publication dates and publications.

Now, going on from there, we have a table of printings, for output. Each printing has one publication date, and one publication, each publication has multiple printings (one for each pub date) and each pub date has multiple printing (one for each pub).

Now, the question, of course, is how to get a portal of ads in the Printings database, for that particular Pub and Pub Date.

The old design, sort of cobbled together before I got here, didn't do the pagination part, so all it did was have a repeating field of pubs and a repeating field of pub dates, and these mammoth scripts that checked each iteration individually on a record before it made a change to these. Then, it would just do a find for the pub date and pub, nad output them thusly.

The apparent solution to me is to have Ad link to an AdInstance DB, that would link to the printings database. Thus, you would have one ad to many adinstances, and one printing to many ad instances. Sadly, this won't work in any direct fashion, because the users have a conflicting set of needs:

1) They need to enter the ads as they always have - select a set of pubs and select a set of dates, seperately from each other (ie, not select a set of printings), unless they specify otherwise

2)They need to, on the other end, have a simple, easy to understand link between printing and ads.

Anyway, that's the gist of it. Any thoughts? Feelings? Ideas? Is there a way I could generate my ad instances database from Ads, Pub Dates, and Pubs? Anyone have some theory on three way many-to-many relationships?

[LiveOak]

It sounds like what you need is a file in which a records is an Ad/Pub/Date. A given Ad would have a record count = #Pub x #Dates. Ad entry would be done using global fields and a "Submit" button. A script would then take over and determine the Publications and Dates selected and create the appropriate records in this new file. Through the clever use of multi-keys, you can then display different selections from this new file (one Ad, one Date, one Publication, etc.) in portals in the other files.

The "theory" is that many-to-many relationships are resolved into one-to-many relationships using a "Join" file in which each record is a combination of the smallest atom of information (Ad/Pub/Date).

-bd

[keshalyi]

I thought I might have to, but my question is this: how can I automatically generate this table, by them entering dates and pubs in each ad? The problem is, I can't set up a simple portal to the ad occurence database, because, basically, if they select three pubs, then make a date, it would need to make three occurences in the ad occurence database. Then if they select another date, it would have to make three MORE occurences. Then if they select another pub, it would have to make two more occurences. Etc.... is there a good way to set this up?

[Vaughan]

Mulitple date keys in each related record?

[LiveOak]

As I said, have a screen with nothing but global fields for data entry. One field might be a checkbox field with a value list for publications. Two more fields might be start and end dates. The "submit" script would take all this information and create the needed records, one for each publication/date. If you have three publications checked and start and end dates that comprise 7 days, your script would create 21 entries in the "join" file in response to the user clicking the "submit" button.

-bd

[keshalyi]

Alright, let me be more particular, I was in a bit of a hurry when I wrote my question before. You may be telling me the answer, but unfortunately I think my cough syrup is slowing my brain...

Alright, I can definitely see your checkbox field (although, I didn't know you could have it select multiple checkboxes, without it being a repeating field - can you?), but the dates are a problem. See, the only solution I see your way is to, in fact, have a start and end date. This wouldn't work, they need to be able to choose any valid dates, in any sequence, skipping dates in between sometimes. Some ads run every two weeks, some every three, some run for a week around easter, two weeks around the fourth of July, and then 6 around Christmas... etc. Start and end date just aren't feasible. They need to be able to dynamically expand the available dates in the pubdates db, with any date so long as its a wednesday (and that part of the process I can do fine - validating that its a wednesday). So, here was my thought -

Is there a way to have a portal show only the domain of a field, not every entry of the field? So that, even though I have, say seven seperate entries in the ad occurence DB on the Xth of Y, it will only show one of them in the portal? Then all I'd have to do, is plop this portal on, and stick on a button to add a date, that automatically duplicated out a date for each of the pubs selected, etc. Or is there another better way> Thanks SO much for your help, and I'm sorry if I'm just missing the boat here...

[LiveOak]

You don't need repeating fields. When you format a text field for a value list with the checkbox display, checking more than one box just enters each select item into the field separated by a carriage return. Create a field, put two copies of it on a layout and then format one copy as a checkbox with a value list. Check a few items an then see what appears in the normally formatted field.

As to your date entry, one option is to just enter a list of dates. Another would be to create an interface where you click on dates in a calendar display and they are highlighted as you click on them. Another option is to have multiple options, by week, by day, etc. A good way to think about your date entry format, is to ask yourself how you take down the information on a piece of paper when you book an ad.

The key here is to stop worrying about HOW to implement the input screen and worry about WHAT input screen to create. What is most convenient for the user?

The input screen is just a scratch pad full of global fields, the actual records are NOT created as the information is entered. A script triggered by an "Enter" or "Submit" button tied to a script you will create will sort of all the record creation and the like. Similarly, the display of the information in your join file can be sorted out using compound key and multi-key relationships. Perhaps the best display would be a calendar with the names of publications on the dates the ads run in these publicaton.

Lastly, I don't think this isn't going to shake out to be as simple as one portal and clicking on the empty row to enter information, unless you want to manually enter each publication/date and put up with the display of all these entries. It also sound like you may need to get some "hands on" help with this solution. Many of the techniques that would make the entry and display of ad information easy and simple are advanced when compared to your current FM level. For instance, calendar display of ad occurrences.

-bd