Unanswered: Structuring Tables for Multiple Project Types
I have worked with Access on and off for many years (took some classes) and pick it up pretty well. I am designing a database for a fund-raising company which will function like an ERP program (ie it will handle production cues, sales cues, track relationships with customers, employee information, etc) and I am unsure how to proceed with the table structure.
The company will have 4 basic project types, Consignment, Shrink Wrap , Mail Order, and Donation. (others may be added down the road) Within each project type there will be multiple products (EX. Consignment = Discount Card, Coupon Checkbook, Scratch off Card, etc).
For each individual fundraiser I want the sales rep to select a project type, then the product that will be sold. This will bring up a customized menu giving them information pertinent to that specific product.
If a rep is selling a Discount card they will need to have a list of businesses for the back, keep track of design and proofing dates (each card has a unique design) Drop dead dates for card production, Card packets stuffing info, prize fliers etc.
If they are selling a pre-fab candle, they will need a unique label design, prize fliers, but no business sponsors or production dates.
The information being asked for depends on the product being sold. In the end I want all of the design requests from each of these products to filter into one central production cue.
What is the best way to structure my tables for this so that I don't congest the database with a bunch of empty records?
Also if you have attempted something like this please give me any design suggestions I am still just setting up basic table structure. Sorry for the long question. Thanks a bunch.