Too simple to be true? ...is this design proper?

[cochese]

New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.

All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.

It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:

A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:

Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix

...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.

Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix
Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price

In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.

Now, will the way I did it hold up? Is there a better, more correct way to do it?

Thank you for the time & help!

 

[georgedwilkinson]

Is this a reporting database or an OLTP (real-time processing) database?

If OLTP, it's all wrong.

If reporting, it's up to you, but you might want to read up on Star Schema, Snowflake Schema, Cubes, etc.

 

[cochese]

It's just for reporting. I'll read up on those schemes. Thank you.

 

[cochese]

Is this a reporting database or an OLTP (real-time processing) database?

If OLTP, it's all wrong.

If reporting, it's up to you, but you might want to read up on Star Schema, Snowflake Schema, Cubes, etc.

Ok, I read up on them, and I'm still lost! hehe. I don't think a Star schema would make sense because I'm only entering end of day numbers, not many transactions.

 

[georgedwilkinson]

It sounds like what you are doing is fine, as long as it works for you. Don't get too caught up in the theory and normalization if there is no need for it in your case.

When your database is too big for Access, you'll need to start worrying about such stuff.