What is the best way to normalize this data?

[keun]

I have these projects, and usually everything runs according to defaults, but sometimes we need to change the specs.

This is a processing tool. We process electronic data by extension. We designate which extensions are processed by each plugin. For example, the Adobe Acrobat plugin processes extension .pdf. The Excel plug in processes extensions .wq1 .xlt .xlv .xlw .wk4 .xls .wk1 .wks .csv .xlsx .xlsm .xltx .xltm .xlsb .xlam .wk3 .xl .xlb .xlc .xld .xlk .xll .xlm.

And so on.

It might happen that a particular project might not want the Excel plugin to process some of those extensions, so we will deselect those extensions for that project. I was thinking that each plugin needs a table for the possible extensions associated with that plugin. But then I thought that perhaps one table can handle all of them. Or I can have an extensions table, a plugin table, and a third table which hooks the plugins to the extension... But that does not seem like it will work because the relationships are not static.

Any thoughts on this mess?

 

[PHV]

I can have an extensions table, a plugin table, and a third table which hooks the plugins to the extension
This is the standard way for a many-to-many relationship.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keun]

The part where I get hung up is - how do I freeze the "hook-up" table when I make changes that are specific to a project?

 

[MajP]

You would need a juction table for projects, plug-in, and extensions

juncTblProject_Plugin_Extension
projectID_fk (foriegn key to a project)
pluginID_fk
extID_fk


Now you can uniquely identify the plugins associated with a project and the unique set of ext for that plug in for that specific project.

I assume the majority of the time the extensions are constant for a given plug-in. But you modify this data for a given project. So I would have a table of default info which includes all possible extensions for a plugin.

tblDefaultPluginExtensions
pluginID_fk
extID_fk

I create a new project. Pick a plugin, and I would have it prompt me what extensions for that plug-in to include for that project. Probably use a multiselect list box. Then it would run an insert query to put this info into
juncTblProject_Plugin_Extension

Now your table has only the extensions unique to that plugin for the given project.