Table Relationships (1 Viewer)

I’m trying to define a relationship between tables. However, the unique nature of my data doesn’t seem to be allowing typical relationships. Because of my inexperience, I’m not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. FYI, I’m only querying the data – no updates.

I have two tables that I’m able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables.
For example:

Table1.ColA has a 5 character string.
Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.


IMPORTANT FACTORS:
- Table1 contains data at a SYSTEM level.
- Table2 contains the parts that make up the ‘SYSTEM’ in Table1
- The PARTS in Table2 can be in 1 to many SYSTEMS from Table1
- Table1.ColA is not unique by itself (it’s part of a composite PK in Table1).
- Table2.ColA is not unique by itself (it’s part of a composite PK in Table2).


Because of the confidential nature of the database, I can’t provide samples. However, what I’m more interested in is the higher level design principles that I should or shouldn’t use.
As I said I’m new to this and want to make sure I build a solid foundation before the database begins to grow.

Any help/advice is greatly appreciated!

Thanks in advance,

Alex

I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.

Click to expand...

Since you are asking how to design your database, the correct answer is this: make Table2.ColA into 2 fields. When designing tables you need to store every discrete piece of data seperately. If you have this code "BX-75Green" where 'BX', '75' and 'Green' all represent distinct pieces of information, then you don't store that code in 1 field, you store the data it contains in 3 fields.

plog said:

Since you are asking how to design your database, the correct answer is this: make Table2.ColA into 2 fields. When designing tables you need to store every discrete piece of data seperately. If you have this code "BX-75Green" where 'BX', '75' and 'Green' all represent distinct pieces of information, then you don't store that code in 1 field, you store the data it contains in 3 fields.

Click to expand...

Plog,

Thanks for the reply!
What about the relationships though? I stll have no FK's. Do I need them in a DB that I'm not updating, just querying?

I don't really understand what you are getting at. Can you provide a specific set of data to illustrate your point?

Maybe this will help: My point was that in designing a database you store every discrete piece of data in its own field. If 2 tables that are now linked by 1 field with 4 parts of data in it, they would be linked by 4 fields once those tables are structured properly.

Further to plog's comments, here is an article that is often referenced when discussing Design concepts/principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Let me try to say this another way. If you have a field that contains "BX-75-Green" and could have a field that says "BX-72-Green" and another that says "BY-75-Green" - and they all mean something distinct - then you have not reduced your information correctly, particularly if these participate in relationships.

If the BX, number, and color-name are independently variable, then they should be independent (separate) fields in your table. You can still declare a compound key of the letter code, number code, and color code - a three-field key - if needed. The more important point is that if you then needed to reference the letter and number (but could omit the color) for a relationship, you can have separate indexes, or a second compound key on only the parts you need.

Let's take a contrary case to finish the illustration. Let's say that you have a very long part number but it is purely a long numeric string and not possible to break up into a department code, type code, or whatever. There, a single field is the entire key and no subset of the field has distinct meaning. You would not have a case where you needed to relate something else to a subset of that part number (because by the definition I just gave, there IS no viable subset.)

The summary is that if the thing you are using as a key is divisible, then bite the bullet and divide it so that you can use the divisions separately where appropriate.