MYSQL tables question about how to do what I want to do

[crainey69]

Hello all! I've been pondering this for months and even asked my previous college professor from Relational database class and he didn't have an immediate answer.

I work for a copier/printer repair business. Besides repair, we sell consumable supplies and equipment. We have a person that handles all of the service and parts on machines and a person that handles all of the consumables and equipment.

I'm trying to create a database that basically handles parts, consumables, and assets (equipment). I think I can figure out how to handle all of that except for one area. When we buy a piece of equipment, I want it in our inventory by model number showing how many we have of that model number with each item having a unique equipment id and being in an asset list so once it's sold we can track the machine throughout it's life.

When we sell that asset, we need to create a invoice. Well any of you that have followed me to this point know that I want to keep that machine as an asset but take it out of inventory and assign it to the person that bought it (temporarily because we may get it back in the future). In the future, it may come back into inventory as an end of lease machine or trade in. I want to be able to follow that machine until it goes into the dumpster and be able to track service on it.

How can I set up the tables so all 3 of our categories are easy to invoice but at the same time separate in a way?

Thanks!

Cory

[pete_1967]

Basic way:

For tracking the assets, create a table that has status codes (e.g. 'l' for lease, 's' for sold, 'd' for discarded etc) and add its primary key as foreign key in your equipment table.

Create customer table and lookup table that links customer's id to equipment id (one customer may have many machines but one machine can only be kept by one customer)

Sort of this way:

table: appliance
--/id = 1
--/status_id = 1

table: status
--/id = 1
--/description = 'on lease'

table: cust
--/id = 1

table: customer
--/id = 1
--/cust_id  = 1
--/applicance_id = 1

Now when you need to find who has what, you query the lookup table by cust id and retrieve appliance ids which you then use to get appliance data.

For status, query should be rather straightforward since each appliance can have only 1 status at one time.

You can then update the lookup table when the appliance is returned for example.

Above is just quick example how you can do it so you may need to refine it but it shows you the principle. I think your professor must have misunderstood your question (or I am) and therefore couldn't give you an answer, or he shouldn't be doing his job.

[crainey69]

ok thanks sounds good!

[ValHolla]

I have used Fab Force's DB Designer to assist in creating databases in the past.
you may want to give it a look.

DB Designer 4
which has apparently been replaced by MySQL Workbench
MySQL WorkBench