Database Design

[kruger101]

Hi all

I've created a database from scratch. No data attached anywhere yet - I'm planning to input the data manually through my Form.

Well I have about 15 tables - CustomerInfo_tbl is the main table with the field RefNumber my primary key (it's an Autonumber which is sequentual). The rest of the tables are normal tables which I plan to link.(these tables also have RefNumber fields which are LongInteger) All the tables have about 10 fields in them.

So then I have 2 choices - I can make one big table (about 150 fields) or 15 tables with 10 fields each and the join them. I heard somewhere that it's better to have small tables and then link them up. So I'm going the smaller tables route.

I'll tell you what I did. I joined all the tables to my CustomerInfo_tbl with a one-to-one join. Then I made a Query, and then I set the RefNumber of the other tables equal to the RefNumber of my CustomerInfo_tbl.

I have 1 problem. When I enter some data into the form (I do not enter all the fields I leave some blank) it isn't displayed in the query, but it is displayed in the appropriate table. Only when I enter all the fields it is displayed in the query correctly and also in the tables.

Any help? I hope I made my question clear enough.

Thank you in advance.
Cheers
kruger101

 

[RV]

I joined all the tables to my CustomerInfo_tbl with a one-to-one join. Then I made a Query, and then I set the RefNumber of the other tables equal to the RefNumber of my CustomerInfo_tbl.

This indicates that in fact you've got one flat table, sliced up in pieces.
In other words, it means that your data structure is not normalised yet.

Do a search on normalization and Codd, either here or on the Internet.
You can find very helpfull sites, such as this one:

http://www.functionx.com/access/

Have a look at sample databases (the Northwind database is a good starter).

If you think you understand these topics, put your daily processes on paper, and start building your structure based on the principals of normalization.

RV

 

[WindSailor]

Nice link RV...

Kruger...
Normally I don't reply to various normalization threads simply because I have my own issues with normalizing a database. Long story. Anyway for a learning experience, look at the following links, make a few hundred pots of coffee and try to take your database (if you have time) to 'Fifth Normal Form'.
I didn't really understand relational database theory until I pushed it to a higher level of Normal Form.
You don't have to keep it at that high of normalization, but taking it to that level will force you to get away from an 'Excel' way of doing things or flat tables. I believe you can have different levels of flat tables with relationships and still not be normalized...

http://www.access-programmers.co.uk/forums/showthread.php?t=100211

http://www.utteraccess.com/forums/s...1203148&page=3&view=collapsed&sb=5&o=&fpart=1

That should keep you busy for awhile...