Sub-classing tables in MS Access

JihadJesus

I'm working on a database design, something I haven't done in about 5 years, and I've run into an issue I'm not sure how to solve. I have a number of major entities that are basically composed of sub-classes, and I'm not sure how to implement the design. Basically I have something like 'Landlords' as the entity I'm primarily interested in, but this group falls into either 'individuals' or 'companies'.

Now, I know I can create kind of sub-tables based on 1-to-1 relationships but a lot of the data will then be much harder to refer to. For example, I'll almost always need to retreive the SSN or EIN if I'm referring back to the Landlord table, but this will be off in the subtable. Making it worse, these sub-class tables are EVERYWHERE, so they end up nested (ie, 'Vendor' could be a Landlord, Utility Provider, etc, some of which have sub-classes) or even tied up in mant-to-many relationship (ie, I'll need to create junction entities for some of these things).

I'm completely stumped by this and just can't wrap my head around how to even start the basic design. I'm going to be refreshing myself on general referential database design basic, but have any of you dealt with this before? Is there some concept I'm just missing?

Deebaser

Hmmm....
Can you create tables for 'entity', 'vendor', landlord', etc and then use a 'support' table to establish your relationships to the tables with usable data?

Example

Entity_nam Entity_id Support_role Support_id
"Bobs_House_of_Entities" B100001 Vendor B123456
"Bobs_House_of_Entities" B100001 Landlord B654321
"Bobs_Landlord" B654321 [Null] [Null]
"Bobs_vendor" B123456 [Null] [Null]

Deebaser

fuck that looked a lot better in the quick reply box...
pretend that's like, ummmm space delimited

Goetterdaemmerung

I think we need more information on how you're going to use (present/enter) the relational data.

Generally, I think your 1-to-1 relationships idea is the best one. Databases (SQL tables) don't have a concept of subclassing, but as you've intuited, it's easily imitated with 1-to-1 table relationships.

And I don't buy your complaint that data will be harder to refer to. In your example, a Landlord's subclass-table has a number you're interested in -- either the SSN or EIN. These are, however, intrinsically different numbers (with different length/formats, validation), so it doesn't make sense that you should be able to refer to a Landlord's SSN/EIN the same way for a Landlord-individual as for a Landlord-corporation.

A better concern is how you need to present the information: do you want to have a "Landlord" form which has a field labeled "SSN/EIN/whatever:"? Probably not, because while this sounds like it might work for SSN/EINs, there are going to be other fields for individuals that corporations have no analog for.

Off the top of my head (until you tell us more how the data is entered/viewed/reported), I think it would work better if your Landlord Detail form looked like this:

a) a section for basic landlord information, that applies to all landlords
b) a tabbed section, one tab for each possible subtype, each tab with its own "subclass-specific" layout, sourced from each of the subtables. Tab visibilities default to False.
c) when you load a Landlord record, you figure out* which subtype(s) apply, and set the visibility of that tab (those tabs) to True.

(*) the most straightforward way would be to put a Subtype field in the Landlord table, and use that to figure out which subtype subforms (tabs) to load.

More info plez

edit: Deebaser's idea sounds like (but isn't quite) a junction table. You don't need this; it's only useful for many-to-many relationships, and at worst you need 1-to-many (if you have a Landlord that needs both Corporation and... Individual? subclasses).

JihadJesus

Thanks for the advice. I think the problem I was having is that I was trying to keep the EIN/SSN as the primary key in the primary table and the sub-class tables since they're the same number of digits. This was causing all kinds of headaches when trying to create or edit records, and it's ultimately redundant info. I'd prefer to enter this primary identifying info in the main landlord table but that doesn't really make sense when they're actually two different typed of ID numbers for each of the subgroups. I suppose I should probably just refer to the Landlord entity by an autonumber primary key throughout the database, and have it look down to pull up the EIN/SSN as necessary in the subforms.

I hadn't thought of having it automatically pull the subforms, I'll have to look into how to implement that. The data I'm most interested in at this point is the status of W-9s for a particular landlord (or vendor more generally) for a calendar year, but I'm going to attempt to design a comprehensive database which is far more complex. I could actually probably get away with just an Excel file for the data I've been told I *need* to track, but I'm upwards of a dozen of those now and it's damned impossible to track down how they relate to each other anymore so it's becoming a hinderance.

[Edit]
Should have been more specific. I need to be able to see what years a particular landlord has filed a W-9 with us based on name/company name/SSN or EIN, the last of these being most critical. I also need to be able to add records for both landlord subtypes and hopefully W-9s as well in a manner that makes sense, preferrably within the same form. I'd also like to be able to see some of the info I've stored on the W-9s, especially a hyperlink I'll have stored to the scanned version of the form.

Goetterdaemmerung

You almost always want to use autonumber keys.

If you're not up on your SQL syntax, be aware that although you can find your way around access without SQL, you can generally do things much more simply and directly by using (say) a SELECT query as a (sub)form's data source.

I probably should have mentioned this first. I just 'finished' creating a medium-sized database application for my parents' business in Access. Often, to do anything truly interesting (like, figuring out which subtypes apply to a Landlord and toggle the visibility of the appropriate subforms), you will need to use VBA. And VBA blows. Hard.

In general, keeping data organized in different tables makes sense. Looking up data in tables is generally very fast, as long as you're correctly using primary keys (which isn't hard). If it makes sense to split part of a table off into its own subtable (like your individual-centric Landlord data), do it. Queries (SQL) give you pretty much infinite flexibility in later combining well-thought-out tables however you want.

JihadJesus

The basic functionality is working much better now, but I'm having some issues with the subform implementation. It seems like I can tie the form to the landlord type fairly easily. What I'd REALLY like to do is get rid of the record controls in the subform - you can navigate away from the right corresponding subform entry and it acts very odd when that happens. Should be able to solve that - I'm going to dig out an old DB I wrote, where I think I managed to implement that kind of restriction.

Goetterdaemmerung

JihadJesus wrote: »

The basic functionality is working much better now, but I'm having some issues with the subform implementation. It seems like I can tie the form to the landlord type fairly easily. What I'd REALLY like to do is get rid of the record controls in the subform - you can navigate away from the right corresponding subform entry and it acts very odd when that happens. Should be able to solve that - I'm going to dig out an old DB I wrote, where I think I managed to implement that kind of restriction.

You can remove the record controls by setting "Navigation Buttons" to no under form properties (for the subform).

But I think what you really need to do is restrict the records that form is able to show by restricting the record source -- if the record source only includes one record, there is no danger of it changing to another. A record source doesn't need to be an entire table; you can either set it to a table and use a filter to filter down to a single record, or use a SELECT query that returns only one record (i.e. "WHERE ID = " & some number).

Also set Allow Additions in the form's Data property tab to false.

JihadJesus

It's coming along; I was wondering if you could look at my VBA code to update the subform. The basic implementation I think is fine, but I can't actually get it to run. I think the current form and after_update methods just....don't end up getting called somehow? Not sure what's going on there. Code's in the spoiler.

Sub ShowSubform()
'Display appropriate subform based on landlord_type chosen

If landlord_type = "Individual" Then
frm_IndividualLandlords.Visible = True
frm_CompanyLandlords.Visible = False

ElseIf landlord_type = "Company or organization" Then
frm_IndividualLandlords.Visible = False
frm_CompanyLandlords.Visible = True

Else
frm_IndividualLandlords.Visible = False
frm_CompanyLandlords.Visible = False

End Sub

Private Sub Form_Current()
'Call showsubform routine to display appropriate subform based on current landlord_type.
ShowSubform
End Sub

Private Sub AfterUpdate()
'Call subform routine to display appropriate subform based on
'update to lanlord_type
ShowSubform
End Sub

Private Sub BeforeUpdate(Cancel As Integer)
'Call subroutine to display appropriate subform
'Should display no subform due to null data
ShowSubform
End Sub

I will eventually be using the form as a lookup, so the records should end up bound to the information for one record (or I guess one corresponding record from each table) based on a search for spme data elements. That'll be fun to implement for me; I've done it before but damned if I can remember how I got it to work. One variant of this will get paired down and set as a data entry tool that's launched with a button.

[Edit]
Damn I hate coding, even for simple crap. Got it to enter the Form_current to get to the main function and borked an error. Bah.

Goetterdaemmerung

No, VBA is just a heinous language.

I can't really give any input on the code (past syntax errors) or design because I don't know how you're linking or showing the subforms. If you really can't figure something out, post for me your accdb file.

JihadJesus

Eh, I'll just have to spend some time on it. I hate coding in general, and VI've got very limited workw ith VBA (the last time I had to write a DB). Ugh.

JihadJesus

Goetterdaemmerung wrote: »

You can remove the record controls by setting "Navigation Buttons" to no under form properties (for the subform).

But I think what you really need to do is restrict the records that form is able to show by restricting the record source -- if the record source only includes one record, there is no danger of it changing to another. A record source doesn't need to be an entire table; you can either set it to a table and use a filter to filter down to a single record, or use a SELECT query that returns only one record (i.e. "WHERE ID = " & some number).

Also set Allow Additions in the form's Data property tab to false.

Alright, got the VBA working and set all of the input filters etc. This is what I'm dealing with now, since as you progress through the form into the subform it's still easy to populate multiple sub-class records for each one in the 'main' table'. I have no idea why it doesn't complain about that when I've created it as a 1-to-1 relationship, but it doesn't.

I think what I'll end up doing is using this form as data entry and then creating a separate set based of a querie to lookup and modify existing data. Doing that all in one place feel like a terrible idea, but if tying it to a select record source works it might actually be okay - it'll refuse to create duplicates of the important info so I shouldn't end up with duplicate records.

[Edit]
Nevermind, I forgot to re-establish the relationships after I redcreated the design. Now, if I can just set the subforms to auto-create a new record when I create a new main record of the same type I should be good to go.