RElationships and different dataset types.

[The Archn00b]

Hello

Quick question:

Say I have two different dataset types.

Type A cam be stored in two tables, a main data table (call it Main_Data) and a metadata (Call it Metadata) table which contains information on Main_Data. There would be a one to many relationship between the relevant parent (Metadata) and the child (Main_Data) field.

Type B can be stored in just a single table, which would be similar to Main_Data. There is no need for an equivalent Metadata table.

However I would like to store the two types together. I am planning on keeping the structure described in the second paragraph. IN this case, Type B data would be divided into the Metadata and Main_Data tables and linked through a one to many relationship also. However the use of the one to many relationship would be completely redundant if I were to be just storing type B data. A one to one relationship would suffice. Or I would just keep it in the same table!

So my question is, is the structure that I am proposing (one to many relationships for some data that really could be stored as one to one relationships or in the same field) inefficient with respect to database structuring guidelines?

Thanks!

 

[The_Doc_Man]

We may have a nomenclature problem here. Metadata in the traditional sense applies to (for example) the Fields collection attached to a table, query, or arbitrary recordset. You get one record of metadata for each field in the table or query. In the parent-child sense, however, metadata is a collection of descriptive properties of the fields and the same set of field descriptors apply to every record in the table or query. I guess you could strain the point and say that a given table or query is the child for which the metadata is the parent. However, I don't think Access will let you enforce that in the normal sense, because it has its own mechanism for metadata. Further, you cannot "turn off" that implicit relationship because it is intrinsic to the COM interface or ActiveX interface or whatever name you prefer.

I suppose that having offered a philosophical discussion, we can get down to the nitty-gritty. What did you propose to DO with said metadata. That might help us figure out a way to do what you really wanted.