nbrass
asked on
Consignment Database Structure???
I am setting up consignment database for a friends store. I have 2 related questions. The database of consigned inventory includes brand#, model#, quantity, condition of item, etc fields. First Q: When it comes to selling these consigned items, items sold may not equal items consigned. What's the best way to setup the data structure to handle this? Second Q: How should sales be handled? - Posted to another table i.e. 'Sold Items Table'?? or recorded as a 'negative' quantity of that item????
Thanks for any constructive suggestions...
Thanks for any constructive suggestions...
I would be inclined to it up this way:
Table 1: Inventory -- Item ID, Description (incl brand, model, condition, etc.), Received Date, Requested Sales Price, Sold At Price (to be completed after sale of item), Sold Date, Commission Rate (if this amount varies from item to item or is negotiated), Consignor Paid Checkbox, Foreign Field: Consignor ID
I don't think I'd break out brand, model, etc. into separate fields. Since this is a consignment shop, many of the items will be unique (unless they deal in one specific type of merchandise). A parameter query can be set to find keywords (such as brand) within the description field. Again, unless they deal with one specific type of merchandise, the results of such queries are likely to be manageable in number.
In a consignment shop, each item must be tracked individually, since consignors are only paid for items sold and items can be sold individually. For instance, you would not want to create one record for "10 widgets rec'd on 8/30/98 from J. Doe" when 4 widgets can be sold and 6 returned to the owner. Every item is tagged with the identity of the owner or ??? In the above scenario, each item would have it's own ID, and multiple items can belong to one consignor.
Table 2: Consignors -- Consignor ID, Names and Addresses of persons placing items for consignment.
Table 3: Buyers -- If the shop owner wants to keep a list of buyers for marketing purposes...
Query 1: Sales -- Sold Date Between XX/XX/XX and XX/XX/XX, Calculated field: total of sold at prices
Query 2: Accounts Payable -- Inventory Table where Sold Date is not "" and Consignor Paid Checkbox = No.
Forms might include Consignor w/Inventory subform, Inventory form based on Parameter Query for specific item ID, Inventory form based on Parameter Query for keywords
What kind of items are in the shop? This might help to further define the database...
Colleen
Table 1: Inventory -- Item ID, Description (incl brand, model, condition, etc.), Received Date, Requested Sales Price, Sold At Price (to be completed after sale of item), Sold Date, Commission Rate (if this amount varies from item to item or is negotiated), Consignor Paid Checkbox, Foreign Field: Consignor ID
I don't think I'd break out brand, model, etc. into separate fields. Since this is a consignment shop, many of the items will be unique (unless they deal in one specific type of merchandise). A parameter query can be set to find keywords (such as brand) within the description field. Again, unless they deal with one specific type of merchandise, the results of such queries are likely to be manageable in number.
In a consignment shop, each item must be tracked individually, since consignors are only paid for items sold and items can be sold individually. For instance, you would not want to create one record for "10 widgets rec'd on 8/30/98 from J. Doe" when 4 widgets can be sold and 6 returned to the owner. Every item is tagged with the identity of the owner or ??? In the above scenario, each item would have it's own ID, and multiple items can belong to one consignor.
Table 2: Consignors -- Consignor ID, Names and Addresses of persons placing items for consignment.
Table 3: Buyers -- If the shop owner wants to keep a list of buyers for marketing purposes...
Query 1: Sales -- Sold Date Between XX/XX/XX and XX/XX/XX, Calculated field: total of sold at prices
Query 2: Accounts Payable -- Inventory Table where Sold Date is not "" and Consignor Paid Checkbox = No.
Forms might include Consignor w/Inventory subform, Inventory form based on Parameter Query for specific item ID, Inventory form based on Parameter Query for keywords
What kind of items are in the shop? This might help to further define the database...
Colleen
This is a different perspective:
I'd have an inventory table, describing what products are available to be consigned/sold, and a Quantity Available which keeps track of items received, consigned, and sold by date and part number. This would allow you to do any calculation you wanted to determine inventory on hand, inventory consigned, and inventory sold. Hope this helps.
I'd have an inventory table, describing what products are available to be consigned/sold, and a Quantity Available which keeps track of items received, consigned, and sold by date and part number. This would allow you to do any calculation you wanted to determine inventory on hand, inventory consigned, and inventory sold. Hope this helps.
ASKER
There is no detail in your answer. I was not able to get any insight from your reply. I have received a very detailed answer from another person ckayter who have given me the type of detail that I was looking for. Thanks for your time though.
ASKER
Thank you Colleen (ckayter) for your detailed answer. In response your your question in the reply, the db is for a consignment china store. Structure of items is very rigid i.e. mfg and pattern etc are determined by the 'china catalog' and does not need the flexibility of various descriptions. I will embellish a little more on the detail in the inventory to include fields like product grade etc which is assigned when the merchandise is entered into the system after proper inspection of the items. I do have a followup question which I did not know if I clearly understood from your response. If 10 teacups were being consigned, must they be entered as 10 separate items or is there some way that they can be entered just once. I may be asking alot but the end users will probably be asking me the same question. I know that each item is a unique item and from my thoughts on the issue, my client may come back to me and say that items can't be broken up i.e. 10 teacups must be sold as 10 teacups. I don 't know for sure yet as I haven't received an answer yet. If they have to be entered individually when being consigned, how will the effect the sale?? Will they have to choose 10 teacups or will they be able to see that there are 10 total in the inventory and just select them????
Looking forward to hearing from you!
Looking forward to hearing from you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I feel like I'm listening in on someone else's conversation! The product table can have a descriptor called "quantity". Different quantities of the same product would have different product numbers, e. g.;
Product Number Item Quantity Cost
0001 China Cup 1 1.00
0002 China Cup 10 9.50
If, however, the quantity varies only based on the number ordered (the customer can order as many single cups as they want, but wants to treat it as a single order), you use the second table I laid out or the Sales table in Ckayter's answer and use an Order number as the key field. In this case, you would have:
Order Part Number Quantity Cost Status
cust001 0001 1 1.00 Consigned
cust002 0001 10 9.50 Sold
rec01 0001 150 50.00 Received
You can distinguish receipts from sales from consignments, and manipulate them any way you want. Let me know if you have any more questions.
Product Number Item Quantity Cost
0001 China Cup 1 1.00
0002 China Cup 10 9.50
If, however, the quantity varies only based on the number ordered (the customer can order as many single cups as they want, but wants to treat it as a single order), you use the second table I laid out or the Sales table in Ckayter's answer and use an Order number as the key field. In this case, you would have:
Order Part Number Quantity Cost Status
cust001 0001 1 1.00 Consigned
cust002 0001 10 9.50 Sold
rec01 0001 150 50.00 Received
You can distinguish receipts from sales from consignments, and manipulate them any way you want. Let me know if you have any more questions.
nbrass,
thanks for the additional information. Got some additional ideas for you. Will be back to you within the hour...
Colleen
thanks for the additional information. Got some additional ideas for you. Will be back to you within the hour...
Colleen
Hulllooo kaldrich. A consignors' table IS a suppliers table. Glad you like my ideas...
nbrass,
Since it is a china shop, your client will probably have both situations: open stock (per piece purchases allowed for items which are rare/valuable) and per lot/full set purchases for items which are plentiful.
Here's how I would build the consignment china shop database:
Table 1 (this, in essence, is a "products" table): Inventory
>Item ID
>Mfg. -- Combo Box -- Lookup from Mfgs. Table
>Pattern -- Combo Box -- Lookup from Patterns Table
>Quantity
>Piece Description -- Lookup from List
>Condition
>Rec'd Date
>Term of Consignment (how long will the item be available to buyers?)
>Commission Rate -- Only if variable by type or negotiable -- Omit if all items are at same and fixed rate.
>Partial Qty. OK? -- Checkbox -- indicates whether qty must be sold as a lot or open stock is allowable (This may actually be a consignor decision, who may not want 3 teacups left over from a set of 12...)
>Price -- UGH! Can't decide the best way to do this... Per piece (qty=x) or as a set (qty=1)? See sales details below... maybe a lightbulb will come on for you that is eluding me... Alternatively, if open stock sale is not allowed, client may want to simply input qty=1 and note number of pieces in memo field. In which case, price is per qty.
>Return Date (When was this item returned to consignor and removed from inventory?)
>China Catalog Pg. Ref.
>Consignor ID (Foreign Field)
>Memo
The purpose for separate mfgs. and patterns tables is the obvious one-to-many relationship. In forms, set up a combo box which would limit the list of pattern choices to those of the selected mfg. (allowing additions, of course).
Depending on how commissions are determined, you may want to include commission information in the Pattern Table and create a lookup field in the Inventory Table.
Table 2: Manufacturers Table
>Manufacturer ID (I'd use a shortname here rather than number)
>Manufacturer's Name
>Country
>History
>China Catalog Pg. Ref.
>Rare Patterns (Memo or Text Field) -- Some patterns are extremely difficult to find and your client may want a field such as this to alert the store's rep of a good consignment or purchase.
Your client may also want additional information fields about the manufacturer's such as a brief description of what they manufactured. (China, Bone China, Porcelain, Ironstone, Pewter, Crystal, etc.) If existing manufacturer's items are rarely taken on consignment, addresses could be handled in a memo field.
Table 3: Patterns Table
>Pattern Name (ID # or shortname is unnecessary. This field plus the mfg's ID would create the primary key)
>Manufacturer ID (foreign field)
>Description
>Composition -- List Box (China, Bone China, Porcelain, Ironstone, Pewter, Crystal, etc.)
>Mfg. Period -- What is the range of years during which this pattern was manufactured?
>China Catalog Pg. Ref.
>Rarity -- List box ranging from buy it outright (the holy grail) to accept only as agent for ready buyer (dime a dozen). Alerts store's rep. of relative value.
>Rare Pieces -- Within most patterns, there are some rare items (i.e., Blue Willow Fingerbowls).
Table 4: Consignors Table
>Consignor ID
>Consignor Name, Address, etc.
>Source for: Pattern(s)
Table 5: Buyers Table -- May be limited to prospects for items sought if store does not track customers
>Buyer ID
>Buyer Name, Address, etc.
>Pattern Interest(s) -- Enables query for specific pattern(s) -- Store rep. can notify when items are received matching this buyer's interests
>Notes: May include things like "Do not call unless items rec'd are in mint condition." "Specifically looking for Friendly Village 36" long platter."
Table 6: Sales Table
>Transaction ID -- AutoNumber??? Since this is not the type of business where the daily receipts are input into a cash register without any concern for the origin of the merchandise...
>Sale Date -- Default to today's date if transaction is input at time of sale.
>Inventory ID
>Buyer ID -- May be omitted if store does not track customers...
>Payment Method -- Useful in determining whether or not it is worthwhile to continue accepting AE and paying their percentages...
Table 7: Sales Details
>Transaction ID (foreign field)
>Item ID (combine w/transaction ID for primary key -- by itself, may not be unique in the event of open stock sales) Lookup from Inventory Table -- No additions allowed.
>Quantity -- Validation Rule checks for less than full quantity and verifies that open stock purchase is OK. (Inventory:Qty - Sales:Qty not equal to 0 AND Partial Qty=True)
>Sale Amount -- I would use the total sale price per item ID regardless of quantity. Reason: Per Set Pricing. If there are 12 finger bowls listed in inventory qty. and price is tagged at $75.00/set, we have 1 set, which would violate the Qty Validation Rule. You could not do qty*price/unit=sale amt. unless you first did the math 75/12. UGH!
A second option for Sale Amount might be to input the tagged price. If Partial Qty=false (sold as a set), then Price*1 = Extension Price (calculated field in form) Else Price*Qty = Extension Price. See discussion in Inventory Table above...
You probably already have a list of queries in mind, but here are a few more I'd add:
Query 1: Pattern Sources (From Inventory Table, Append to Archive)
Records with return dates are periodically archived. This way, your client will be able to search for items returned to consignors which may be requested by potential buyers after the item is no longer in inventory.
Query 2: Accounts Payable -- Query Sales Table for all transactions occurring within past 30 days (or however frequently consignments are paid)
Query 3: Revenues -- Query Sales Table and generate calculated fields of commissions earned, total income.
Consignment Acceptance Form:
>Manufacturer -- Combo Box
>Pattern Name -- Combo Box
>Pattern Table Fields
>Subform: Inventory records matching pattern
>2nd Subform: Buyers records w/interests matching pattern
Subforms could also be done quite elegantly on a second tab...
Sales Form:
>Buyer Table Fields (for input)
>Sales Table Fields (for input)
>Subform: Sales Details Table Fields (for input)
>2nd Subform: Inventory Record Fields linked to Subform field in focus (to verify that item identified matches inventory record).
>Total Sale (calculated)
>Sales Tax (calculated)
>Grand Total (calculated)
I think if I were spec'ing this job, I'd also want to find out if the store ever purchases items from consignors (as in the case of very rare pieces which did not sell during consignment period). If so, then you'll need to consider how to track items which are owned by the store. You could simply create the store as consignor ID 0001 and exclude that ID from Accounts Payable, and add if...then statements to the commissions calculation in the Revenues query.
I considered transferring store-owned items to a separate table, but it really makes a mess of the sales details and sales form...
Forgive me if some of this sounds kind of convoluted. I'm brainstorming as I'm writing...
Any other special circumstances that need to be considered???
I like this application. Certainly a lot more fun than the bail bonds application I just finished (whole new meaning of many-to-many) and the manufacturer's rep job I've just started (can you say C-A-L-C-U-L-A-T-E-D F-I-E-L-D -- to the max.) Thanks for sharing this one!
Hope this helps and maybe triggers some ideas of your own...
If you choose to accept this as an answer, I will resubmit it as an answer (unable to do so at this time -- the question is locked while there is an answer pending).
Colleen
Colleen
nbrass,
Since it is a china shop, your client will probably have both situations: open stock (per piece purchases allowed for items which are rare/valuable) and per lot/full set purchases for items which are plentiful.
Here's how I would build the consignment china shop database:
Table 1 (this, in essence, is a "products" table): Inventory
>Item ID
>Mfg. -- Combo Box -- Lookup from Mfgs. Table
>Pattern -- Combo Box -- Lookup from Patterns Table
>Quantity
>Piece Description -- Lookup from List
>Condition
>Rec'd Date
>Term of Consignment (how long will the item be available to buyers?)
>Commission Rate -- Only if variable by type or negotiable -- Omit if all items are at same and fixed rate.
>Partial Qty. OK? -- Checkbox -- indicates whether qty must be sold as a lot or open stock is allowable (This may actually be a consignor decision, who may not want 3 teacups left over from a set of 12...)
>Price -- UGH! Can't decide the best way to do this... Per piece (qty=x) or as a set (qty=1)? See sales details below... maybe a lightbulb will come on for you that is eluding me... Alternatively, if open stock sale is not allowed, client may want to simply input qty=1 and note number of pieces in memo field. In which case, price is per qty.
>Return Date (When was this item returned to consignor and removed from inventory?)
>China Catalog Pg. Ref.
>Consignor ID (Foreign Field)
>Memo
The purpose for separate mfgs. and patterns tables is the obvious one-to-many relationship. In forms, set up a combo box which would limit the list of pattern choices to those of the selected mfg. (allowing additions, of course).
Depending on how commissions are determined, you may want to include commission information in the Pattern Table and create a lookup field in the Inventory Table.
Table 2: Manufacturers Table
>Manufacturer ID (I'd use a shortname here rather than number)
>Manufacturer's Name
>Country
>History
>China Catalog Pg. Ref.
>Rare Patterns (Memo or Text Field) -- Some patterns are extremely difficult to find and your client may want a field such as this to alert the store's rep of a good consignment or purchase.
Your client may also want additional information fields about the manufacturer's such as a brief description of what they manufactured. (China, Bone China, Porcelain, Ironstone, Pewter, Crystal, etc.) If existing manufacturer's items are rarely taken on consignment, addresses could be handled in a memo field.
Table 3: Patterns Table
>Pattern Name (ID # or shortname is unnecessary. This field plus the mfg's ID would create the primary key)
>Manufacturer ID (foreign field)
>Description
>Composition -- List Box (China, Bone China, Porcelain, Ironstone, Pewter, Crystal, etc.)
>Mfg. Period -- What is the range of years during which this pattern was manufactured?
>China Catalog Pg. Ref.
>Rarity -- List box ranging from buy it outright (the holy grail) to accept only as agent for ready buyer (dime a dozen). Alerts store's rep. of relative value.
>Rare Pieces -- Within most patterns, there are some rare items (i.e., Blue Willow Fingerbowls).
Table 4: Consignors Table
>Consignor ID
>Consignor Name, Address, etc.
>Source for: Pattern(s)
Table 5: Buyers Table -- May be limited to prospects for items sought if store does not track customers
>Buyer ID
>Buyer Name, Address, etc.
>Pattern Interest(s) -- Enables query for specific pattern(s) -- Store rep. can notify when items are received matching this buyer's interests
>Notes: May include things like "Do not call unless items rec'd are in mint condition." "Specifically looking for Friendly Village 36" long platter."
Table 6: Sales Table
>Transaction ID -- AutoNumber??? Since this is not the type of business where the daily receipts are input into a cash register without any concern for the origin of the merchandise...
>Sale Date -- Default to today's date if transaction is input at time of sale.
>Inventory ID
>Buyer ID -- May be omitted if store does not track customers...
>Payment Method -- Useful in determining whether or not it is worthwhile to continue accepting AE and paying their percentages...
Table 7: Sales Details
>Transaction ID (foreign field)
>Item ID (combine w/transaction ID for primary key -- by itself, may not be unique in the event of open stock sales) Lookup from Inventory Table -- No additions allowed.
>Quantity -- Validation Rule checks for less than full quantity and verifies that open stock purchase is OK. (Inventory:Qty - Sales:Qty not equal to 0 AND Partial Qty=True)
>Sale Amount -- I would use the total sale price per item ID regardless of quantity. Reason: Per Set Pricing. If there are 12 finger bowls listed in inventory qty. and price is tagged at $75.00/set, we have 1 set, which would violate the Qty Validation Rule. You could not do qty*price/unit=sale amt. unless you first did the math 75/12. UGH!
A second option for Sale Amount might be to input the tagged price. If Partial Qty=false (sold as a set), then Price*1 = Extension Price (calculated field in form) Else Price*Qty = Extension Price. See discussion in Inventory Table above...
You probably already have a list of queries in mind, but here are a few more I'd add:
Query 1: Pattern Sources (From Inventory Table, Append to Archive)
Records with return dates are periodically archived. This way, your client will be able to search for items returned to consignors which may be requested by potential buyers after the item is no longer in inventory.
Query 2: Accounts Payable -- Query Sales Table for all transactions occurring within past 30 days (or however frequently consignments are paid)
Query 3: Revenues -- Query Sales Table and generate calculated fields of commissions earned, total income.
Consignment Acceptance Form:
>Manufacturer -- Combo Box
>Pattern Name -- Combo Box
>Pattern Table Fields
>Subform: Inventory records matching pattern
>2nd Subform: Buyers records w/interests matching pattern
Subforms could also be done quite elegantly on a second tab...
Sales Form:
>Buyer Table Fields (for input)
>Sales Table Fields (for input)
>Subform: Sales Details Table Fields (for input)
>2nd Subform: Inventory Record Fields linked to Subform field in focus (to verify that item identified matches inventory record).
>Total Sale (calculated)
>Sales Tax (calculated)
>Grand Total (calculated)
I think if I were spec'ing this job, I'd also want to find out if the store ever purchases items from consignors (as in the case of very rare pieces which did not sell during consignment period). If so, then you'll need to consider how to track items which are owned by the store. You could simply create the store as consignor ID 0001 and exclude that ID from Accounts Payable, and add if...then statements to the commissions calculation in the Revenues query.
I considered transferring store-owned items to a separate table, but it really makes a mess of the sales details and sales form...
Forgive me if some of this sounds kind of convoluted. I'm brainstorming as I'm writing...
Any other special circumstances that need to be considered???
I like this application. Certainly a lot more fun than the bail bonds application I just finished (whole new meaning of many-to-many) and the manufacturer's rep job I've just started (can you say C-A-L-C-U-L-A-T-E-D F-I-E-L-D -- to the max.) Thanks for sharing this one!
Hope this helps and maybe triggers some ideas of your own...
If you choose to accept this as an answer, I will resubmit it as an answer (unable to do so at this time -- the question is locked while there is an answer pending).
Colleen
Colleen
Thanks, Colleen for correcting my ignorance! (which is vast; any correction I can get I take!) The only additions I have to Colleen's well-thought-out solution are some of the issues involving cost can be resolved by breaking out Inventory and Products into separate tables (so you can keep track of costs & product-related discounts in the product table) &, if you are big enough, you may want to have a table of discount rates by customer type. (You see this in the consumer products industry where Wal-Mart gets a better deal than Joe's market because they can be expected to order more over a year's time.)
ASKER
Thanks for your continued involvement in answering my questions. I honestly haven't had time to think through Colleen's thorough answer but I will be doing that tonight. My friend will be working again today and I will call her to get confirmation on some of the questions that have been raised in our correspondence. It is really great to have knowledgeable people like yourself and Colleen to work with - I can't tell you how much your help means to me.
Nicole
Nicole
nbrass,
Why did you accept kaldrich's answer if you're going to consider the structure I proposed??? I read your user info, so I know you're new to E-E. This is how the program works:
Once you've accepted an answer, the point value you placed on the answer are awarded to that expert and although further comments may be added to the question, it is considered closed and will move to the answered questions section. Then if you have additional questions concerning the feasibility of the ideas proposed, you'll have to ask another question and pay additional points for it.
Colleen
Why did you accept kaldrich's answer if you're going to consider the structure I proposed??? I read your user info, so I know you're new to E-E. This is how the program works:
Once you've accepted an answer, the point value you placed on the answer are awarded to that expert and although further comments may be added to the question, it is considered closed and will move to the answered questions section. Then if you have additional questions concerning the feasibility of the ideas proposed, you'll have to ask another question and pay additional points for it.
Colleen
ASKER
You're right I am new and I did screw up. I am sorry about that. I wasn't clear when the question at the bottom said are you willing to accept the answer that he proposed. I had, I thought, already accepted your answer. I wanted only to accept his addition '2 cents worth' as he was continuing to participate. I didn't realize that he would be awarded the points or that this closed the Q&A session. Do I have any recouse or is that the 'last word'?
Nicole
Nicole
To Colleen (hope you're monitoring this, still). I haven't had any points show up for this answer on my ID. If they do, I'll be happy to put an equivalent number into a question that you can just answer and I'll give you credit for it. (It was your detailed answer that was accepted; you're right in that you deserve the points.) Let me know.
Kevin (kevin.aldrich@cna.com)
Kevin (kevin.aldrich@cna.com)
ASKER
Thanks Kevin for your offer. Sorry about my screw up!
Nicole
Nicole
Nicole, that's okay. E-E newbies are cut a little slack... If you need to continue this commentary, feel free to make it a low point question. I'll be around...
Kevin, don't worry about it. As you know, points awarded to you and points you spend for questions are not in the same pot, so to speak. Besides, you might need them... I know I would if I was trying to get Access 2.0 to work with Word 95 to work with Access 97... YIKES!!! However, I will expect you to keep an eye out for questions from nbrass, so we can continue this thread if necessary.
Three heads are better than one (even if it does look kinda funny!).
Colleen
Kevin, don't worry about it. As you know, points awarded to you and points you spend for questions are not in the same pot, so to speak. Besides, you might need them... I know I would if I was trying to get Access 2.0 to work with Word 95 to work with Access 97... YIKES!!! However, I will expect you to keep an eye out for questions from nbrass, so we can continue this thread if necessary.
Three heads are better than one (even if it does look kinda funny!).
Colleen
ASKER