need good database structure for tracking counter state

[Lost User]

Dear, Can anyone help me to make good database structure? What i have are some locations where I have some machines. Every machines have 2 counters (IN and OUT). I need to be able to enter state of that counters and after that to compare that values with previous entry IN and OUT counters. How I see it seams that somewere I need to keep information wich machine is on wich location. Beside that I need to track information about date and time of entries. Situation: Location 1 -> machine1, machine2, machine3 Location 2 -> machine4 Location 3 -> machine5, machine6, machine7, machine8 .... I need 3 kind of reports: ○ Deference between new IN / OUT and some old IN/OUT counters for every machine ○ Total IN/OUT for some location ○ Total IN/OUT for all location How I need to organize this datas? Thanks for helping me, akioki

[Mycroft Holmes]

3 tables

Location
LocationID int identity(1,1)
Location varchar(20)

Machine
MachineID int identity (1,1)
LocationID int
Machine varchar(20)

TranTable
TranID identity (1,1)
MachineID
Direction
Date

This assumes a machine can have only 1 location and the machine can be checked in/out many times. Using a date on the transaction table will allow you to get durations, and state on a given date

Never underestimate the power of human stupidity RAH