Advice on tables 1

[MelissaBerrett]

Hello!
I need some advice on creating tables. There are 24 different facilites, each with seperate check registers. To start my DB, do you think it would be better to have 24 different tables to store the info, or one large table. (There's about 50 lines of info comming in per facility everyday, which I am appending in from excel). I was thinking seperate tables, but there is nothing to link them with. Is it good to have your tables linked? Thanks!!

Melissa

 

[LittleSmudge]

First off Melissa you need to remember that this is an international site and that words link facilities, check, register have very different meanings depending on which version of English you are using.

So before I can get into specifics,
Is check the English or American usage ? ( Eg. A Bill Of Exchange or a Verification Process ? )
Is facility a tool or a building?
Is register a list or a machine?
etc.. ..

In general terms:-
All of the fields in a table should be relational ( As in Relation Database! ) That is they must all relate to a specific entity which itself becomes the primary key for the table.

If there are items of data that can occur multiple times for a particular primary key value then they should be stored in another relational table of their own and a relationship ( one-to-many ) established between then.

If you have two tables that store identical data except for one field - or even worse identical data and the only difference is the source of the data - then you should be storing the information in the same table. Add one more field to specify which one of the 24 sources the data came from.


As an example:
Retail shops, each shop has between 3 and 5 'departments' and every hour the departments independently report sales totals to a central database


tblShops
ShopId
ShopName
ShopSize
ShopManagerRef
etc..

tblDept
DeptCodeId
DeptName


tblSales
ShopRef These first two fields are ForeignKeys
DeptCodeRef that link to the two tables above.
SaleDate
SaleHour The bold 4 fields are the join PrimeKey
NettSales for this table
SalesTax
TransCount

NettSales, SalesTax and TransCount are the data fields and each department will add one record to the table every hour.

You can then do simple select queries to find out sales by store or department over whatever time period you want.

You can even feed the data into CrossTab queries for further analysis.

I think your structure is going to need to look something like this.
If its way off mark then explain why.




'ope-it-'elps.

G LS

 

[MelissaBerrett]

I apologize! In this circumstance:
facility - building
check - bill of exchange
register - list

For each building there is a list of checks for each day that are in an excel file. I import them to a table, then use an append query to get the information into one table. The table ends up looking like this.

BlgNo. | CheckNo. | VendorNo. | Payee | Amount | Today

10 | 1234 | 1010 | Bob | 14.21 |8/28/02
10 | 1235 | 1456 | Sue | 13.25 |8/28/02
10 | 1236 | 115 | Joch | 14.61 |8/28/02
15 | 1237 | 120 | Grag | 45.63 |8/28/02
15 | 1238 | 3 | Jim | 13.51 |8/28/02
15 | 1239 | 4510 | Rom | 170.00 |8/28/02
15 | 1240 | 5610 | JAck | 1000.00|8/28/02

(Except there are about 40 lines of information for each building Number appended everyday, making one HUGE table) I know that it is bad to have duplicate data in a table. But I don't understand how I could break up the information and still be able to import it from excel. Or, is it okay to have a table this large? Thanks!!!
Melissa

 

[LittleSmudge]

So you have 24 facilities generating 40 records each = 1000 records per day.

If you have them in one table or 24 tables you will still have 1000 records per day.

How long do you need to keep this data ?

How long do you need to keep this data ACTIVE ?
Eg. After the end of the month can last month's data be archived into another table ( maybe even in another database ) to keep the current data table "lean & mean" ?

What are you going to actually be doing with the data - I gave you an idea or two about what you could do with it in the example above. Is this even vaguely close to what you want to do ?

Over-riding concern is get the structure right for the data flow. - Coping with the quantity of data must be subordinate to structure - because without the correct structure you have no reliable data.
( And to be honest, something less that 200,000 records for a full year is NOT a huge amount of data. - Especially as there are only a limited number of fields in any one record. )


'ope-that-'elps.

G LS