Need conceptual help with weird data model
Need conceptual help with weird data model
- Subject: Need conceptual help with weird data model
- From: Markus Ruggiero <email@hidden>
- Date: Mon, 29 Aug 2011 17:29:23 +0200
Hi,
I am working with a legacy database that I am not allowed to change because other apps access the db and they cannot be changed. Fortunately those apps access the DB in a read-only way, so I can add tables and attributes to existing tables. There is one area that the developers in the past have implemented in a weird way. I am trying to extend the existing structure in a way that allows my D2W app to function properly.
We have one table called STATEMENT. A statement can be used as title (boolean flag yes/no), has a unique code (not an id but a user visible string), a description and a label. The external app reads a product table and these statements and via a join table generates a PDF file for the product. The join table entries tell the program, which statement(s) must be included in the PDF report. The label attribute for the statement is what is printed in the PDF. Each statement has a sequence number and each statement has a boolean flag that denotes it as being title or not. Title title statements have sequence numbers like 1000, 2000 etc and non-title statements have respective sequences 1001, 1002, 2001, 2002, etc. So the grouping of statement under its title is given by the range of sequence numbers. In addition a statement can have several variants. These variants all have their own code. However the label field is identical. So to collect all variants for a given statement the app today has to look for statements in the same sequence region with the same label text. We therefore have a 3-level hierarchy where the hierarchy is completely done in code based on assigned number ranges and user editable text (the label attribute).
I need help in designing a clean model that works in D2W and then somehow tie it to the existing STATEMENT. I can even go so far as to replace STATEMENT by a view (3rd party app is read-only on the db).
Thinking along these lines:
- add a foreign key to STATEMENT to get statement belonging to title, and variant belonging to statement. But not every statement has variants. Would I need a qualifying intermediate table?
- create a separate hierarchical table to build title<->>statement<->>variant and have an attribute on all levels pointing to the record in STATEMENT
- create 3 separate tables for the 3 levels? And then? Have these point to the statements or have the statements point to the hierarchy structures?
- I am confused :-(
Any ideas? What would you recommend?
Sorry for bothering but the list is currently my only available sparring partner :-)
Thanks a lot
---markus--- _______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden