By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

table design help

P: n/a
trying to design a db for a company that has 20 customers that take
product from a pipeline. This product is metered and totaled at
midnight for the past 24 hours. What I would like to do is be able to
run queries on this info like how much a certain customer has taken
for the last 90 days etc. I am having a bit of trouble trying to
figure out what tables I need. Should I have a table for each day,
which would get to be quite large after a few months, or a table for
each month, or just a table for the totals with date column.

What I have so far is a table called tblCustomers which has all 20
customers in it.

Anyone doing anything simular or have any ideas?

a nudge is appreciated.

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
First, don't worry about your tables getting large. Your data will not get
near the limitations of Access, either in size or performance.

I would recommend that you keep data in the smallest possible chunks that
you could ever in your wildest dreams imagine having a use for. So if the
smallest unit you can get to is a 24-hour reading, I'd use that. Don't box
yourself in, or lose valuable data that you'll wish later that you had.
Store everything - your mdb will have plenty of room, and if it ever begins
to max out in a few years, it's time to upgrade to SQL Server anyway.

You will need your customer table, and you will also need a daily_meter
table. Your daily_meter table will have fields like:

daily_meter_id (autonumber)
dm_customer_id (Long, foreign key to table customer)
dm_reading (Long - I assume this is the actual meter reading - not sure of
the format)
dm_usage(Long - I have no idea of the units/amounts, but when in doubt, use
a big number just in case)
dm_updated(Date - the date/time stamp of the update)

If the data is entered manually (hopefully not), you will also need:

dm_update_by(Text-25 - to record the name of the updater.)
Darryl Kerkeslager
"David C" <dc*******@cox.net> wrote:
trying to design a db for a company that has 20 customers that take
product from a pipeline. This product is metered and totaled at
midnight for the past 24 hours. What I would like to do is be able to
run queries on this info like how much a certain customer has taken
for the last 90 days etc. I am having a bit of trouble trying to
figure out what tables I need. Should I have a table for each day,
which would get to be quite large after a few months, or a table for
each month, or just a table for the totals with date column.

What I have so far is a table called tblCustomers which has all 20
customers in it.

Nov 13 '05 #2

P: n/a
David C wrote:
trying to design a db for a company that has 20 customers that take
product from a pipeline. This product is metered and totaled at
midnight for the past 24 hours. What I would like to do is be able to
run queries on this info like how much a certain customer has taken
for the last 90 days etc. I am having a bit of trouble trying to
figure out what tables I need. Should I have a table for each day,
which would get to be quite large after a few months, or a table for
each month, or just a table for the totals with date column.

What I have so far is a table called tblCustomers which has all 20
customers in it.

Anyone doing anything simular or have any ideas?

a nudge is appreciated.

Thanks


I would have at least 2 tables; customer table and metered table.

The customer table would hold name, address.

The metered table would have fields to store the customerid, date, and
the meter info.

You may have some look up tables to assist in displaying data.

Then when you add a record for the date, you have a drop down of the
customer list, and enter the other required info then move onto the next
record.

You can easily determine all orders for customers (specific or all) with
in a date range or select all orders in a data range and all customers
associated with the orders.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.