Need some DB design input

[dgmorr]

Hey guys,

I'm creating a database based on an Excel file. The Excel file has about 180 columns and are only linked by to distinct fields. Is there any possible way that I can break this down into easier to manage tables? There are different categories of columns such as

Financial Info
Personal Details
Products
Requirements

I'm not too sure how to link these categories other than the two distinct fields I've mentioned and I know it's not a good idea to have 5 tables with identical primary keys across all 5. Can anyone offer some input?

 

[theprez]

What you are talking about is the essence of relational databases.

It is preferable to have separate tables, each table containing related fields.

So if I had a basic contact data that has

ID Name Address ...
100 J. Lowe 22 Elm
123 R. Redd 33 Oak


In relational terms you really should only create a separate financial table if there are a lot of fields that you really need to separate or there are multiple financial records for one person for intance

ID Year Income .....
100 2004 $56,000
100 2005 $58,500
100 2006 $59,000
123 2006 $84,000

Hope this helps.

 

[dgmorr]

Thanks, theprez.

What I'm curious about is whether or not it is dumb to share the same primary key across all tables. In this sense, I might as well just keep them together, but since they are in their own categories, I would like to separte them.

Is it out of the relational design to keep something like...

ID Name Address ...
100 J. Lowe 22 Elm
123 R. Redd 33 Oak

ID Year Income .....
100 2004 $56,000
123 2006 $84,000

ID Car Mileage
100 Honda 200
123 Toyota 300

ID Gender Colour
100 Male Blue
123 Female Orange

 

[KeithG]

Is one ID going to have more than one car? Is one ID going to have more than one Salary record? If so you need to use ID as a FK (Foreign Key) and create a different primary key for these tables and create a one to many relationship.

 

[The_Doc_Man]

dgmorr, in response to your specific question...

From what I see, these items share one partial key in common, but they are improperly linked by that key.

Specifically, that one that shows "Income" has a year in it. Let's focus on THAT table as an example of why your layout is not normalized. You can then decide how the other fields might - or might not - be candidates for their own separate tables.

The normalization rule that applies here is that the data in the table must depend entirely and only on the primary key of the table and nothing else. You show TWO salaries that differ by year. Therefore, the salary field does not ONLY depend on the primary key you have assigned - because that key is incomplete. The CORRECT key is the person ID and the year as a compound key. After which the salary entry depends on person and year - which makes sense logically as well as verbally.

I would import this spreadsheet to a big whompin' table with NO keys to speak of. But then, I would run a series of make-table queries to populate the real tables that you need. (Or define the tables and their keys, then populate with append queries.) Draw your data from the big, unstructured table.

The whole thing is going to become an exercise in defining which attributes are dependent (salary in the focus example) and which ones are independent (year and ID in the focus example). Related data elements belong in the same table. Related, INDEPENDENT elements are what are called CANDIDATE KEYS - options for becoming all or part of a prime key. When you have only one independent element, it is a single prime key. When you have multiple independent elements, assuming that the logical separation has already occurred, you have possible compound prime key members.

The trick, of course, is to analyze the structure well enough to properly normalize everything BEFORE you attempt to do that set of append queries I described earlier. Without normalization, you can still do a lot of good things, but Access is going to balk at some of the nicer things you could ask it to do.

 

[theprez]

Do a search for normalization on this forum. There are great references/comments about it.

In your case anything that is unique to a record can/should be in the same table (i.e. gender, name, etc...). The problem was you have so many fields you may want to break it up.

If however you find you can have multiple records for one record, you will have to create a separate table. For instance, using the example I used above, if you have financial data for different years for the same entity, you will have to create a table for financial data and use 2 keys to uniquely identify each record (ID, Year).

ID Year Income .....
100 2004 $56,000
100 2005 $58,500
100 2006 $59,000

Hope this helps.