DB structure

Franknstuff

More I know Less I know
Local time
Today, 12:45
Joined
Apr 28, 2004
Messages
54
Do to recent forum talks, I've been questioning my own DB structure.
The question is I import large data blocks which have duplicate names.

Name RefDes Internal Name
LM339 U1 ICLM339
LM339 U2 ICLM339
RES12 R3 RE12F

I know I should split this into multiple tables, but I'm not sure what is the best way? :confused:

I want to make a solid foundation.
 
This fist question I would have is are LM339's always equal to ICLM339, etc.?
 
Usually duplicate data is a candidate for a separate table. I can't tell from the data, but LM339, from your example, does it have other data items related to only it? If it does than those data elements could be placed in their own table, and then a foreign key pointing to that row could be used in this table. But if you are importing this data you would need something like a import table, and queries to break the data up and assign foreign keys. This would normalize your data better :D. BUT (and this goes against the grain) is it an issue for you :confused:? Sometimes the effert to "make things right" is more an issue than what you are doing. If the data is messy, and you have problems reporting (or whatever), than normalization would gain you control over those processes that you are having problems with (usually) :cool: . But if it works fine the way it is, why fix something that aint broke :rolleyes: ?
 
Thanks for responding

The data is:


Device: Reference: Internal Name:
radio R1 RES10k
radio U2 LM339
radio R3 RES10k
clock R1 RES20K
clock R2 RES20K
clock R3 RES5K

I would like to fix it now will I'm starting instead of later when everything is in place. I thought of importing into a table and using query's to make tables for each column tieing everything together using ID numbers.
 
no

This fist question I would have is are LM339's always equal to ICLM339, etc.?


No. This is where I ran into issues later.
 
Without knowing the entire scenario, I would be afraid to comment further. Looks like a row (Entity), represents a subcomponent of a larger item (attribute 1) with a quick reference type field (attribute 2), to identify the subcomponent within the larger item.

Is this correct? If not:

Q1. What is a row of data representing in the real world.
Q2. How many rows do you deal with at a time.
Q3. How many fields (more than 3?)
 
Ok

The rows of data have to relate to each other. I'm not asking for someone to do it I'm just asking what direction should I take. There seems to be little information on how to import and distribute data into tables while keeping the data integrity. All I want is to import data and distribute the data to the appropriate existing Tables while keeping the data integrity.
 
Still need to know what you're doing in the real world (non database terms), to advise you on how to import the data into normalized tables...
 
I can't

Your asking more than I can give. If you need more than thanks anyways.
 
Looks like flight details to me. Make up fictional ones and then you can post.
 
OK, I'll give you the "professorial" response since you can't supply a detailed explanation of the data you are storing.

In general, you are mapping something when you design a database. If it is related to business, you are mapping tables to business entities. If it is your home CD collection, you are mapping tables to entertainment entities. Etc.

So start by looking at what you are trying to do. Here is Doc's Rule: If you can't represent it on paper, you will NEVER represent it in Access. Sometimes I use sticky notes to design tables, using a white-board with dry markers to draw relationship lines. OK, the next questions are RHETORICAL and you don't have to answer them to me. Only to yourself.

First question: What are you representing? Abstractions? Concretia? Both? Each time you identify a new thing to be represented, it will either be a new record in a "thing" table or a candidate for being the basis of a new table.

Second question: How many distinct types of things are you representing? You should never have more basic tables than you have types of things, and often you have less tables.

Third question: How often does a descriptive term come up in your records? When you can identify a commonly-used description or attribute, you just found a candidate for a table listing those attributes.

In the final analysis, you are building tables to represent entities related to the real-world business or process you are defining. For instance, in a business you would have employees, leading pretty quickly to an employee table. You might have sales, leading to a sales-order table. You might have departments at your office, leading to a department table.

Let's try another topic. Suppose you are cataloging your CD collection. You have a bunch of CDs. OK, there's a candidate table. They tend to be spread over several categories of music. There's a candidate (attribute) table - the category/genre. They are by various artists. There's another candidate table - the artist list. Each one has several tracks with distinct titles. There's a candidate - the selection-title table.

Do you see the mind-set you have to use? Things in tables can be concrete (the CD) or abstract (the genre). But they represent discrete things or facts or attributes you want to track. And they almost automatically indicate relationships. I.e. CDs have tracks, genres, and artists.

This is the first step in defining tables. Spend some time up front before you try to write too much or cast too much into stone. Because it makes a big difference down the road if you get it right quickly and don't have to go back to retrofit every few days or weeks.
 
Hope I don't get into to much trouble

Here is a sample database.


I used the Analyze Table in Access to split the data. What I have problems with is appending another file to the three existing tables?
 

Attachments

Users who are viewing this thread

Back
Top Bottom