在Django中重构MS Access前端MSSQL数据库表结构是否可行？

I'm building a Django web interface for a legacy system that uses MS Access as the frontend and MSSQL as the backend. We have two test benches with similar functionality but different data reporting methods:

• The old test bench stores all test results in a single flat table, where each test step corresponds to a column.
• The new test bench uses a relational table structure that adapts to variable numbers of test steps.

My goal is to unify the database structures so I can perform similar queries on both sets of data. I prefer the new test bench's structure, so I want to adjust the old one to match it.

Initial Flat Table Structure (Old Test Bench): #

+-----+---------+--------+-----------+----------------+----------+----------------+----------------+----------+----------------+-------------+---------------+
| ID  | PartNum | Serial | Pass/Fail | Test A 1 Upper | Test A 1 | Test A 1 Lower | Test A 2 Upper | Test A 2 | Test A 2 Lower | Test B Done | Test B Passed |
+-----+---------+--------+-----------+----------------+----------+----------------+----------------+----------+----------------+-------------+---------------+
| 123 | 991     | 111    | T         | 10.0           | 8        | 6              | 15             | 13       | 12             | T           | T             |
| 124 | 991     | 112    | F         | 10.0           | 9        | 6              | 15             | 16       | 12             | T           | T             |
| 125 | 991     | 113    | F         | 10.0           | 7        | 6              | 15             | 14       | 12             | T           | F             |
+-----+---------+--------+-----------+----------------+----------+----------------+----------------+----------+----------------+-------------+---------------+

Target Relational Structure (Matching New Test Bench): #

Master Table

+-----+---------+--------+-----------+
| ID  | PartNum | Serial | Pass/Fail |
+-----+---------+--------+-----------+
| 123 | 991     | 111    | T         |
| 124 | 991     | 112    | F         |
| 125 | 991     | 113    | T         |
+-----+---------+--------+-----------+

Test A Table

+-----+--------+------+-------+-------+-------+------+
| ID  | TestID | Test | Upper | Value | Lower | Pass |
+-----+--------+------+-------+-------+-------+------+
| 211 | 123    | 1    | 10    | 8     | 6     | T    |
| 212 | 123    | 2    | 15    | 13    | 12    | T    |
| 213 | 124    | 1    | 10    | 9     | 6     | T    |
| 214 | 124    | 2    | 15    | 16    | 12    | F    |
| 215 | 125    | 1    | 10    | 7     | 6     | T    |
| 216 | 125    | 2    | 15    | 14    | 12    | T    |
+-----+--------+------+-------+-------+-------+------+

Test B Table

+-----+--------+------+--------+
| ID  | TestID | Done | Passed |
+-----+--------+------+--------+
| 311 | 123    | T    | T      |
| 312 | 124    | T    | T      |
| 313 | 125    | T    | F      |
+-----+--------+------+--------+

I know this isn't a trivial solution—my main question is: Is this refactoring feasible using Django Models?

Absolutely, this refactoring is totally feasible with Django Models—this is exactly the kind of relational structure Django excels at working with. Here's how you can approach it step by step:

1. Define Your Django Models #

First, model the target relational structure. You'll have a master model, then two child models linked via foreign keys to the master:

from django.db import models

class TestMaster(models.Model):
    id = models.IntegerField(primary_key=True)  # Match legacy ID
    part_num = models.CharField(max_length=50, db_column='PartNum')
    serial = models.CharField(max_length=50, db_column='Serial')
    pass_fail = models.BooleanField(db_column='Pass/Fail')

    class Meta:
        db_table = 'Master'  # Name matches your target table

class TestA(models.Model):
    id = models.IntegerField(primary_key=True)
    test_master = models.ForeignKey(TestMaster, on_delete=models.CASCADE, db_column='TestID')
    test_step = models.IntegerField(db_column='Test')
    upper_limit = models.FloatField(db_column='Upper')
    value = models.FloatField(db_column='Value')
    lower_limit = models.FloatField(db_column='Lower')
    pass_status = models.BooleanField(db_column='Pass')

    class Meta:
        db_table = 'TestA'

class TestB(models.Model):
    id = models.IntegerField(primary_key=True)
    test_master = models.ForeignKey(TestMaster, on_delete=models.CASCADE, db_column='TestID')
    done = models.BooleanField(db_column='Done')
    passed = models.BooleanField(db_column='Passed')

    class Meta:
        db_table = 'TestB'

2. Migrate or Transform Legacy Data #

Since you're working with a legacy MSSQL database, you have two main options here:

• Option 1: One-time data migration
  Write a custom Django management command to pull data from the old flat table, transform it, and populate the new master/detail tables. For example:

  • For each row in the old table, create a TestMaster record.
  • For Test A steps 1 and 2, create two TestA records linked to the master. Calculate the Pass status by checking if Value is between Upper and Lower.
  • Create a single TestB record per master row using the Test B Done and Test B Passed columns.

• Option 2: Use Django's database views (if you can't modify the legacy table)
  If you need to keep the old table intact, you can define database views that mimic the master/detail structure, then map your Django models to those views. This lets you query the legacy data as if it's in the relational structure without modifying the original table.

3. Unify Queries Across Both Test Benches #

Once both systems are mapped to the same model structure, you can write reusable query logic. For example, to get all Test A results for a specific serial number:

def get_test_a_results(serial):
    return TestA.objects.filter(test_master__serial=serial).values(
        'test_step', 'upper_limit', 'value', 'lower_limit', 'pass_status'
    )

This query will work identically for both the migrated old test bench data and the native new test bench data.

Key Considerations #

• Legacy ID Matching: Make sure to preserve the original ID from the flat table as the TestID foreign key in the child tables—this maintains data integrity.
• Data Type Consistency: Double-check that your model field types match the legacy MSSQL data types (e.g., BooleanField for T/F values, FloatField for numeric limits).
• Performance: For large datasets, consider adding indexes on foreign keys and frequently queried fields (like serial in TestMaster) to keep queries fast.

This approach will let you fully unify your data access layer, making it easy to build a single Django interface that works with both test benches.

内容的提问来源于stack exchange，提问作者Benaiah2320