Hi Rickard,
I tried this and generating a logical schema. As I suspected, these
rules maps to one column each in my Project table. I want to avoid
By default, the mapping algorithm will avoid making arbitrary one-to-one
related tables.
colums that depend on anything else rather than the PK as much as
possible. Therefore, I think what I want is something like this:
Project (ProjId PK, ProjName, ....)
ApprovedProject (ProjId PK FK, Status, Date, ApprovedByEmpId)
This allows me to create easy constraints to protect data integrity
within ApprovedProjects.
Ah... You want to use subtypes then. The facts remain pretty much as I
listed them before, but you now introduce 1 to 3 new objects (e.g.
ApprovedProject, RejectedProject, and maybe UnknownProject) that are
subtypes of Project. You can then have facts and constraints distinct for
each (e.g. RejectedProject has RejectReason() ) but keep those common to all
(Approval Code, Date of Approval Code, etc.) at the project level. Then,
there is a mapping option you can set that will create separate tables for
each subtype with a one-to-one inheritance relationship with the Project
Table.
You'll use the subtype arrow for this; the arrow points from the child (e.g.
Approved Project) to the parent (Project).
Allthough I like your idea with U etc for unset status in general, for
this specific instance, I dont want the user to *actively* be able to
set it to Unset <g> and I want to check this with the abscence of
ApprovedProject.
You could do it either way then. Regardless of your decision, you will have
some logic built in somewhere that would make sure it is not null or not U
for certain states. I generally try to avoid nulls (for lots of reasons).
Yes, there can only be one approval (Y/N) in ApprovedProject, therefore
the PK on ProjId.
I still think the Approval Code (Y, N, U) is a fact of Project and belongs
in the project table. I think introducing subtypes as above will alleviate
your concern about column and rule isolation for each type. Further, this
fact will guide the rule as to which child table is actually populated
(ApprovedProject is only populated for "Y", etc.) and as such is a fact
about all projects -- and belongs at the project level -- regardless of
status.
Am I violating any design rule here? Because otherwise it seems strange
that ORM doesn't allow me to make it generate this logical design.
No, ORM can do this as per the above. You could also do it the hard way by
introducing a one-to-one fact type for a brand new object which will more or
less do the above. Subtypes are a better (and conceptually cleaner) way to
handle this.
Any input appriciated. Perhaps I should take this to c.db.theroy?
Hope that helps,
Scot.
................................................
Scot Becker
Orthogonal Software
www.orthogonalsoftware.com
ORM Blog:
www.objectrolemodeling.com
To e-mail me, please use my first name at the domain listed above. All mail
sent to the reply-to address gets routed to a junk e-mail folder where it
will likely be deleted without being read.
