我有两个SQL表：TableA和LookupTable。 TableA将LookupTableID（PK）存储为外键和其他一些字段。 LookupTable存储LookupTableID，PharmacyName，PharmacyAddress，PostCode，PharmacyTelephone，PharmacyFax，PharmacyEmail等。
TableA中的数据以Asp.net形式显示。 表单的每一行都有两个按钮：“打印”按钮和“预览”按钮。 当用户单击“打印”按钮时，将在文档中生成包含来自LookupTable（对于TableA中的LookupTableID）的药房信息以及TableA中的一些其他信息的pdf文档。 此外，TableA中的Printed字段设置为true，并创建TableA中的新记录，打印和新创建的记录之间的唯一区别在于以下字段：StartDate，EndDate和Printed。 在新创建的记录中，StartDate变为“打印记录的EndDate + 1天”，EndDate变为“打印记录的EndDate + 7天”，新创建的记录的Printed为false。
当用户单击“预览”按钮时，将在文档中生成包含来自LookupTable（对于TableA中的LookupTableID）的药房信息以及TableA中的一些其他信息的pdf文档。 请注意，在这种情况下，系统不会创建新记录或设置为true。
我在上面的数据库设计中遇到的问题是：如果其中一列允许修改查找表中的PostCode并且用户点击历史/打印记录的预览按钮，则预览pdf文档将显示最新信息，即新帖子代码而不是打印时的邮政编码。 我知道，这是一种预期的行为。 但是，我想在打印时显示历史记录的信息，以及仅显示尚未打印的记录的最新信息。
我的一个解决方法是将LookupTable视为库表，供用户选择药房，然后将所有内容（查找表中的所有字段）复制到所选药房的TableA。 这将在数据库中创建大量重复数据。 有没有更好的方法来实现我的目标？ 任何帮助，将不胜感激。
I have two SQL tables: TableA and LookupTable. TableA stores LookupTableID (PK) as a foreign key and some other fields. The LookupTable stores LookupTableID, PharmacyName, PharmacyAddress, PostCode, PharmacyTelephone, PharmacyFax, PharmacyEmail etc.
The data from TableA is displayed in an Asp.net form. The form has two buttons for each row of the grid: Print button and Preview button. When user clicks Print button, a pdf document containing the pharmacy information from LookupTable (for LookupTableID in TableA) along with some other information from TableA is produced in the document. Moreover, the Printed field in TableA is set to true, and a new record in TableA is created, the only difference between the printed and the newly created record is in the following fields: StartDate, EndDate and Printed. In the newly created record, StartDate becomes "EndDate from printed record + 1 Day", EndDate becomes "EndDate from printed record + 7 days", and Printed is false for the newly created record.
When user clicks Preview button, a pdf document containing the pharmacy information from LookupTable (for LookupTableID in TableA) along with some other information from TableA is produced in the document. Note that the system doesn't create a new record or set printed to true in this case.
The issue that I have with above database design is: if one of the columns lets say PostCode in the Lookup Table is amended and user clicks on Preview button for historical/printed records then the preview pdf document will display up to date information i.e. new post code rather than the post code at the time of the printing. I know, this is an expected behaviour. However, I would like to display the information for historical records as they were printed, and the up to date information for only those records which are not yet printed.
One work around that I have is to treat LookupTable as a library table for the user to select a pharmacy and then copy everything (all fields from the lookup table) across to the TableA for the selected pharmacy. This will create a lot of duplicate data in the database. Is there a better way to achieve my objective? Any help would be appreciated.
最满意答案
这似乎是一种不必要的复杂且不灵活的方式，可以在每次打印时拍摄数据快照。 看起来更好的选择是对两个表进行版本化并创建第三个表，它只包含TableA ID和打印日期。 无需复制数据。 要在任何特定打印日期重现数据，只需使用打印日期查询截至该日期的版本化数据。 无论从那时起它发生了多大的变化，你都会收到该日期出现的数据。
我给出了类似情况的一个答案是：
https://dba.stackexchange.com/questions/114580/best-way-to-design-a-database-and-table-to-keep-records-of-changes/114738#114738
希望能帮助到你。
This seems like a needlessly complicated and inflexible way of taking a snapshot of your data every time it is printed. It would seem like a much better alternative would be to version both tables and create a third table which would just contain the TableA ID and the date it was printed. There is no need to make a copy of the data. To reproduce the data as it was on the date of any particular print, just use the print date to query the versioned data as of that date. You would get back the data as it appeared on that date, no matter how much it changed since then.
One answer I have given for a similar situation is here:
https://dba.stackexchange.com/questions/114580/best-way-to-design-a-database-and-table-to-keep-records-of-changes/114738#114738
Hope it helps.
更多推荐
发布评论