Help with DB Structure

[Themis Skiathitis]

Hi guys, I have set up a database and while everything seems to be working fine, it just "feels" clunky.

I have created a set based on a Transaction DB with links to a Client DB and also a Stock DB.

Because we run a trade-in style business a lot of transactions involve us getting one or two items from the client as well as supplying one or two items to them.

Sometimes it could be just one item (in/out) and at times it could be 2, 3 or even 4 items.

What I have done is to create a form where the clients details are shown and I then have 4 item in fields (itemin1, itemin2, etc) as well as 4 item out fields.

When a transaction is done everything about that transaction, including prices (pulled via lookup from the stock DB) is shown on the form which is them printed to provide statement or receipt.

I can't help feeling that I should only really have one field for items that could be entered, marked as incoming or outgoing and then saved to allow input of the next item if there is one.

Somehow, I can't seem to see the forest for the trees and any advice or suggestions would be very much appreciated.

[forskare]

RE: Help with DB Structure

I can't seem to see the forest for the trees and any advice or suggestions would be very much appreciated.


What do you need help with? Creating a report?

kenn

[Themis Skiathitis]

RE: Help with DB Structure

Hi Kenn, I was basically looking for some way to structure the set without having to have 4 x item in fields and 4 x item out fields.

I thought it might be easier to have just one field that could be marked as incoming or outgoing but the thing is that I need all items to be shown on the final form something like an instant invoice.

I just can't seem to get my head around whether this would be done with an embedded form, embedded browse, etc.

Thing is if this sounds confusing, its because I'm confused :)

[JohnZaleski]

RE: Help with DB Structure

Your structure is certainly not the traditional way one would do this.
One suggestion would be to have a database similar to Alpha's sample invoice database. The in-out transactions would be line items or child records to the main transaction table.Each transaction would have an "in-out" indicator field.
One problem with your structure is printing historical reports. For instance,with 4 potential "in" items and 4 potential "out" items all on the same record, it will be tricky to create a report showing all the ins and outs by item. However with a
Another obvious problem is if a customer has more than four ins or outs sometime in the future. You've hardcoded a limit into your database.
However, without all the particulars of your business which drive the design, it's hard for anyone to say that your design is "clunky".

[whanigsberg]

RE: Help with DB Structure

Themis,

For sure I don't have a complete picture of your problem but here is a thought.

Conceptually, you are (if I interpret your message correctly) focussing on the client. So when you have to book a transaction you call up a form based on the client and then you must have all kinds of fields for all the different possible transactions.

What if you focussed on the transaction. A new transaction must be booked so you open a form built on the transaction which has fields for type etc etc and then for client which you fill from a lookup.

Then when you want to focus on the client, you can call up a form with client as parent and a unified list of transactions as child.

I am certain that this idea omits necessary elements of your situation but perhaps it will get your juices flowing.

Please report back,
Bill

[David Sander]

RE: Help with DB Structure

Themis,

I would set this up as the following tables and set. Have a Customers table with an incrementing number for each customer you add. This also supplies you with a nice contact list if you need that. Only enter each Customer once so you have a unique list or use field rules to prevent duplicate entries.

Make a second table for your transactions starting with the Customer ID number. These can list either incoming or outgoing items. You can use the check box or manage these in/out fields through clever field rules. I.E. items shouldn't be bought or sold twice for example. You can also assign -/+ values to the items and therefore know the possible profit in your in/out transactions.
You would link this second table to the customers by a one to many link in a set.

Reports for this set are fairly easy to do. All the items for one customer can be listed in order. The totals of the transactions will add up for either a particular sale or a month. You can get reports for incoming totals for the month from all the negative values and compare this to the positive values of everything sold.

Does this sound interesting?

Dave

[Themis Skiathitis]

RE: Help with DB Structure

Hi dave, yes it does sound interesting but my main concern is that I must have a transaction number for each transaction.

If I open the transaction file, call up the client and then enter the item will I not then have to save the transaction for it to allow the input of a second item? Would this not then create a second 2nd transaction record?

What you suggest would be ideal but i need to able to enter say 3 items coming in, sometimes none if it is a straight purchase, and then of course the 3 items going out. This all needs to be saved under one transaction number.

Its actually this part of things that is causing me headaches as I can't seem to come to grips with it hence the system i created with 4 x in fields and 4 x out fields.

Am I missing something obvious here? :) At this stage nothing would surprise. Its been some time since my brain turned to mush and I'm waiting for it solidify again :)

[Keith Hubert]

RE: Help with DB Structure

Themis

I had a similar type of problem. What William and David suggest is about the same but it takes a little understanding to get your head around. Try and think of it like this. You have 3 tables, 1 Header with transaction number, date, Customer lookup, etc. 2 Details table with transaction number and descriptions, prices etc. 3 Customer details. Create a Set starting with the Header 1:1 link to Customer and 2 links 1:M to Details. This will now give you two Child Browses one for goods coming in and the other etc. That way you are not restricted to the number of transactions. And both in and outs would have the same Transaction number for reporting since they are the same table. To keep the form tidy have two tabs one for each browse.

Hope this has added to your ideas pool.

Keith Hubert
London.