db design question - need help

[romulus]

Hello,

I have been wracking my brain for 3 weeks trying to get something to work on my own without any success so I am hoping for a few pointers here.

I have a small comic shop in Indiana and I am trying to get our internal subscription service organized. I need to generate 2 lists. The first list is the customer want list by comic title. The second list is to find every title and give me a number of how many are needed for that title.

Now I have been able to create the customer want list from a value list(s). That works fine. But I have been unable to automate the list for the list of books and how many are requested.

I could really use some pointers but I don't want to take up too much space here to ask for help. Any suggestions? I'd be happy to expound on our current problem either here or by direct email. Thanks for your time in advance.

Darin

All-Star Comics & Games

[kennedy]

Not sure I followed that... but here's what I think I heard...

You need a list of customers... just info about the customer... name, address, phone, etc... nothing about comics. May be just a name and unique ID.

Then you need a list of comics... just info about the comic... title, price, source, whatever. Maybe just the title and a unique ID.

Then you need a third file that is customer wants... keyed by customer ID *and* comic ID.

Given that, then, you can easily generate a portal in the customer record that shows all the comics that particular customer wants. And you can easily generate a portal in the comic record that shows all the customers that want that particular comic. You can also, then, using those same relationships, generate Counts and Sums over those lists... easily generating a report of total wants for each comic or each customer.

Did that get you what you were after?

[kennedy]

P.S. If you truly have no info about customer other than name and no info about comic titles other than title, and you are confident that you can use those as unique IDs, then you can collapse the three files into just one... just the third file of wants. But then you need to create the same two relationships with self-joins... and you'll need to add a "FindUnique" capability to get your lists. A search on this forum for FindUnique and user CobaltSky will get you a link.

[romulus]

Hi Kennedy,

I guess I should slow down a bit here.

We do an instore subscription for our customers. There are the several big publishers (Marvel, DC, Image, etc) and then a bunch of indy publishers. I have made a list of the core titles for each of the publishers. I have used those lists as value lists under their respective publishers. For instance, I know customer Brian wants Batman each and every month. So I check Batman. He'll get Batman 608 this month, 609 next, 610 after that and so on.

Right now I have the two databases. One that is the check off boxes and the other that is a master that relates back to the check off form. I chose the value list method because it printed a nice form that I can give to the customer, he(or she) fills out and returns to us. I then check the boxes on the screen. The next form gives me the list by publisher what Brian wants. So far so good. But that only works so well.

I probably need to scrap this system and start from scratch but I am just not that skilled in database construction yet to figure out the best way of doing it.

Ideally I would have a list of customer wants that would give me a nice list of what they want for each customer. Also, I would like a report of how many of a given issue is needed and which customers needed it. And to top it all off, I get a list each and every week in a comma delim. format of what is coming in. So from that list I'd love to be able to parse down Batman #608 to Batman, have that check the list, and report to me that I need 75 copies for subscribers and who those subscribers are.

Does that seem a little much? <grin>

I'm sure I am going to have to scrap my current system to make it better. I just need a few pointers in the right direction.

Thanks again.

Darin

[Kurt Knippel]

Kennedy was right on. You need a minimum of 3 files. Customer.fp5, Comics.fp5 and Subscription.fp5, do not try to collapse them into less files, it is more work and more confusing for a beginner. You may also want to consider adding a Publisher.fp5 file if you wanna track stuff by publisher, but this could be integrated into the Comics.fp5 db.

Start with the basics: Customer.fp5 contains the customer name, and you might as well start getting some demographic information, interests, likes/dislikes, etc as well. Comics.fp5 contains the comib book title, publisher, release frequency, genre, characters, etc. Each of them should also contain a unique ID (see Unique IDs), which will be used to link them together in the Subscriptions.fp5, which would contain both the CustomerID and ComicsID and any other information that you need that is specific to this subscription.

[romulus]

Thanks for the pointers!

Now I have an idea of what to do.

I really appreciate the help.

Darin