September 30, 2004 at 10:51 am
hiya,
I have a schema that I am a bit unsure about. Sorry if this is a bit fuzzy, I have tried to trim it down.
A company can both buy and sell stuff, so I have 2x accountTypes
1) purchaseLedger
2) salesLedger
<schema>
tblCompanyPURCHASELedger
companyId FK
purchaseLedgerId FK
tblPURCHASELedger
purchaseLedgerId PK
accountTypeId FK
field1
field2
tblSALESLedger
salesLedgerId PK
accountTypeId FK
fieldA
fieldB
fieldC
fieldD
<\schema>
I think that the above schema isn’t fully normalised, but I can’t quite explain how L
I want to be able to search on accountType
I realise that I can create the following table..
<schema>
tblAccountType
accountTypeId
accountName
<\schema>
However, there seems little point in adding a “accountTypeId” to tblCompanyPURCHASELedger etc, because I already obviously know what type of account it is (due to the table that it is contained within)
Is there anything glaringly obvious?
I appreciate any feedback.
Ta,
yogi.
October 4, 2004 at 8:00 am
This was removed by the editor as SPAM
October 6, 2004 at 5:50 pm
You are right that the design is questionable. However, technically it's not a normalization issue. (However, you can have fully normalized designs that still have many many issues.)
I would classify this as a mis-handling of a Type / Subtype. You have Ledgers, which presumably share some common attributes (A1, A2, A3). And you have 2 sub-types of them, which subtypes may have a number attributes that only apply to that subtype (like B1, B2 for one and C1, C2, C3 for another).
Logically, there is one clearly "correct" design: 3 tables. One for the super-type, and one for each of the 2 sub-types. The table for the supertype would have all the common attributes (A1-A3) and each subtype table would have the attributes that just extend for that subtype (one would have B1, B2 and another table would have C1-C3). (Caveat: if either subtype has no additional attributes beyond the supertype's attributes, no additional table is needed for it.)
For physical implementation, however, this is hotly debated. For my money, I prefer to implement the correct "logical" design and only back off it when a clearly known performance issue occurs.
Note: the primary key of the supertype should *also* be the primary key of the subtype. It enforces the one-to-one relationship that is correct for type/subtype relationships. I have seen this violated and it's simply wasteful.
For the record, the standard variations are: (a) combine subtype attributes into the supertype, allowing NULLs in cases where they don't apply; or (b) split into 2 tables and duplicate the supertype attributes across both subtypes. Your case is (b) it seems.
In either of these variations, you have data integrity issues to deal with. In my experience, most designs using these variations ignore the data integrity issues and only by doing so can become better performing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply