vendredi 2 décembre 2016

Database design sanity check

folks;

I've written a program that consumes datasources (CSV, fixed-width, tab-delimted) and converts them to SQL datatables. I control the settings for this program in a SQL database. Each datasource I consume is referred to as a Program. Each Program has only one DatasouceDefinition. The DatasourceDefinition is a table that uses the ProgramID as a foreign key back to the Program table. In this way, since I know the ProgramID, I can find the rows in the DatasourceDefinition table that define the datasource for the Program I'm trying to convert to SQL. Now I'm trying to expand this a little. I want to be able to define one set of DatasourceDefinition entities that can then be used to define multiple Programs. My scenario is I have three Programs that use the exact same datasource schema (with a total of 112 columns), so rather than make three sets of DatasourceDefinition entries (3 sets x 112 rows = 336 rows) I would rather make just one set (1 set x 112 rows = 112 rows) and then link that set of rows to three Programs. I'm done a crow's foot diagram to help me see how this could work, but what I've designed seems like bad database design. I'm wondering if anyone has input on how I've setup my proposed changes:

DatasourceDefinitions table design

It seems to me that the connector from [Programs].[DatasourceDefinitionsId] to [DatasourceDefinitions].[DatasourceDefinitionsId] breaks good database design practices. But, the relation displayed by this connector is valid and necessary.

Any advice is appreciated.

Aucun commentaire:

Enregistrer un commentaire