Do I have to break Normalization? 1

[perrymans]

I am working on a configuration database, part of which is associations of Builds.

I have a listbox which has the BuildID and AssociatedID.

The AssociatedID field is my way using a single field to store ID's from diferent types of items (Documents, Forms, Dwg's). The problem is that the ID's are all Autonumber, so I could end up with multiple "22" 's as an ID, one for Document, and one for Form. To avoid this, I could include the Form and Document Numbers into the table, but that breaks Normalization.

I may be able to make the Document and Form numbers the ID fields, but I am trying to avoid that.

The combined field is an experimental work-around to avoid multiple tables storing Build info for each type (tblBuildForm etc..).

Anyone thinking of a better way as they are reading this?

Thanks. Sean.

 

[mpastore]

I would just add a third field that designates object type "F" for form, "D" for document, etc. I don't the E.F. Codds in the crowd will be offended.

 

[perrymans]

I added a Type field after I wrote the thread, with a tblType containing TypeID and Type. A txtBox on the form is populated with the appropriate Type number.

BUT!!!!....

In the single listbox on my form, which displays these items, how do I associate the items based on ID and Type to the appropriate tables?

ID Type
34 1 This may be a document, how can I get the DocNum to show in a third column in the listbox?
34 2 This may be a drawing, in the same box!

What will result in a new look of:

Number
QMF001 (Document #)
FRM567 (Form #)

with the other columns hidden of course (I know how to hide the columns).

Too confusing?

Thanks. Sean.

 

[psemianonymous]

OK, let me retract everything the other two said. This is how you do it:

1. If the three types are using the same data structure, then put them in the same table with an additional field "Type" ... which would be 1,2,3
2. If the types have any differences in data (or maybe the possibility of differences upon expansion), then use three tables and link to all three tables at once using OUTER JOINs. This way you have triple the fields in the query, but this is acceptable.
3. If you must retain your current structure, do a UNION query on the three tables. Basically you say "SELECT tbl1.*, 1 AS fld FROM tbl1 UNION ALL SELECT tbl2.*, 2 AS fld FROM tbl2 UNION ALL etc etc."

I recommend 1 or 2.

 

[perrymans]

Number 2 it is. I was drunk with the love of a single box showing results, but your right, I can just bring the different values together in a query to populate that one Listbox.

This will call for more tables and also complicate my next upcoming thread....Deleting Table Values from a Listbox.... if there is multiple sources.

Thanks again. Sean.