Help with Data Structure and history log!

[robinsonbst]

Hi Guys

I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.

I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.


Any help would be much appreciated.

Thanks
Brad

 

[The_Doc_Man]

I won't try to design this for you because your description is vague and I don't have the time for a full-blown design session anyway. But here are some thoughts on the subject matter.

First, recognize that no DB will ever give you an answer someone else didn't give it first. (Sounds either convoluted or perhaps circular? Good, you were paying attention.) That is, you cannot detect duplicates or returns unless you have an unequivocal way to identify items uniquely. If you don't put some sort of unique identifier on everything you sell, you cannot ask the question of whether you ever sold it before. If that number doesn't make it into the DB, you can never tell you had a returned item. I.e. the unique number has to be there to be recognized. So your first task is not a program design issue but a physical PROCESS design issue. How will you identify returned items?

Second, to most of us, history usually means "includes time/date field." So whatever design you create has to take that into account. Read up on the uses of date/time fields. You will need it for item history reports. (If the item's dates don't make it into the DB, how will you tell one entry from another if two entries involve the same item?)

Third, you imply that a customer can return something after a delay. Does that make a difference in your business model? If that customer sold the item to a third person and that person then brought the item back, does it have the same effect in terms of what you will do? Here, the idea is whether the operation has to consider item and previous owner or just the fact that the item was returned. Also if there is a different action to be taken based on prior owner vs. who tried to return it, does that affect your record-keeping? If so, in what way?

Let me just say that the first part of any design process for a database, whether huge or tiny, is to create it in your mind and put it through its paces. Think of some "thought experiments." Like, what would happen if...

This is where the "old programmer's rule" kicks in. If you couldn't do this on paper, what makes you think Access will do it for you electronically? If you do not have a good handle on your intended algorithm before you start writing code, you are already doomed.

So where do you start? Why, the drawing board, of course! Where do you go when it doesn't work right? Back to the drawing board. (Where did the guy who invented the first drawing board go back to when it didn't work?)