Having some difficulty relating data

[walterbyrd]

Simplified HOA database:
"name" (NameID, Last, First)
"address" (AddressID, City, State, Zip)
"nameaddress" (FkOwnerID, FkResidentID, FkLocalAddress, FkOtherAddress)

The same name can correspond to two addresses (rented out home in hoa, and present residence)

The same address can correspond to two name (owners name, renters name)

I can not do much to change the stucture of the database.

Although I think I understand the way basic data relations work in dataface, I can not seem to create any sort of a meaningful table.

For example, If I was was looking at the "name" table, and I wanted to see the associated address(es), I would first need something like this:

#in the name/relationships.ini file
[nameaddress]
nameaddress.FkLocalAddress = "$NameID"
nameaddress.FkOtherAddress = "$NameID"

Except that doesn't work. And even if it did, I would only have a NameID number.

I hope I'm making sense.

[shannah]

Hi Walter,

Probably your relationship should be something like

[owned_addresses]
nameaddress.fkOwnerID="$NameID"

[residences]
nameaddress.fkResidentID="$NameID"

These will result in a tab for each Name record for "Owned Addresses" and "Residences".Ê The Owned addresses tab will have a list of that name's owned addresses.Ê The "Residences" tab will have a list of that name's residence addresses.

I'm not sure that this is what you wanted to achieve, though.Ê From the sounds of your assumptions about the way relationships work in dataface, I'm guessing that you are looking to create a single table like

MyTable(NameID, Last, First, fkLocalAddress, fkOtherAddress)

This may be possible using the __sql__ parameter in the fields.ini file.

http://framework.weblite.ca/documentation/manual/delegate_classes/method_reference/methodsql

But this would depend on the multiplicity of the relationship between the name and nameaddress tables.

This method (referred to as adding calculated fields to the "name" table) will only work if there is at most one corresponding record in the nameaddress table.Ê This is because the sql query that is used as an alternative for fetching the records of the name table must return the same number of rows as the name table itself (if that makes any sense).

There are other tricks that you can use too in the calculated fields realm that will allow you to customize the columns included in list view.

If you want ultimate flexibility, however, in some cases you may still want create a custom action.

-Steve

[walterbyrd]

It's almost working, but I get both of the values under one tab, and nothing under the other.

It took some work, but I restructured the dbase:

Simplified HOA database:
"name" (NameID, Last, First, FkLocalAddress, FkOtherAddress)
"address" (AddressID, City, State, Zip, FkOwnerID, FkResidentID)

#name database
[owned_addresses]
address.fkOwnerID="$NameID"

[residences]
address.fkResidentID="$NameID"

Again, it works, but both records are listed the owned_address tab, neither record is listed under the residenc tab.

[shannah]

OK.. this new structuring is a little different - and looks like it lends itself to adding calculated fields more easily.

for example, in your fields.ini file you could have:

__sql__ = "select n.*,a1.city as local_city, a1.state as local_state, a1.zip as local_zip, a2.city as other_city, a2.state as other_state a2.zip as other_zip from name n left join address a1 on n.FkLocalAddress=a1.AddressID left join address a2 on n.FkOtherAddress=a2.AddressID"

Then the name table will be listed as one big table with addresses included.

Alternatively you could do this via relationships - but I'm thinking that the relationship would be kind of boring since there will be only one address listed in each relationship.

Another note on your new database design.Ê I'm a little confused about how it all works together.

It looks like you are referencing which addresses a name is associated with directly in the name table via the FklocalAddress and FkOtherAddress fields.Ê However you also seem to be linking addresses to names via the FkOwnerID and FkResidentID columns.ÊÊ Unless these are referring to very different things, it would seem to be redundant (redundant in relational dbs == bad).

-Steve

[walterbyrd]

The HOA wants me to translate an existing, client based, ms-access database to an online database. I do not have the luxery of having things set up in a way that makes sense for dataface. Or, in a way that makes sense at all. The ms-access database was developed by an amateur over several years.

FkOwnerID and FkResidentID are over 90% redundant. There are 1100 homes, and only about 80 are rented. In almost all cases the owner, and resident, are the same. Fairly often, the renter's name is unkown, and the key is set to the bogus NameID (9999).

Should I put owners and residents in seperate tables? If so, I suppose I should put HOA addresses, and other addresses, in seperate tables as well. Something like: owned_addresses, rented_addresses, owner_names, renters_names. A rented home would have an owner's address, and renter's address in different tables.

BTW: does the __sql__ statement go in fields.ini, or relationships.ini? In your last comment, and in the documentation, you said fields.ini.

[shannah]

Redundancy between owners and renters is OK from a design standpoint because they still are tracking different things - even if the owner IS the renter most of the time. The redundancy I'm referring to is the fact that the Name table seems to be referencing the address table via the fkLocalAddress and fkOtherAddress fields AND the address table seems to be referencing the name table via the fkOwnerID and fkResidentID fields.

What does if mean if an address is referenced via the fkLocalAddress field? Does it mean that that name is the owner - or the renter - or does it mean something different?
If it means that that name is either the owner or the renter, then I would ask - what is the point of also specifying this in the fkOwnerID or fkRenterID fields?

BTW: does the __sql__ statement go in fields.ini, or relationships.ini? In your last comment, and in the documentation, you said fields.ini.

fields.ini.

http://framework.weblite.ca/documentation/how-to/how-to-add-calculated-fields-to-your-list-and-details-view

-Steve

[walterbyrd]

An owner can have just a local address, or a local address and another address. For example, an owner may rent his local house to another party, and the owner actually lives out of state, in that case the HOA wants the address of the house that is within the HOA, and the owner's out-of-state residence. With renters, the HOA is only concerned with the local address.

If I know a name, and I want an address - or addresses, I could look in the name table and find both the local address, and any other address. At least that's the idea.

There is also a "rented" field in the address table. Although, that is not always accurate.

On the other hand, if I have an address, and I want a name, I can look that up in the address table. I may want the name of the person who actually lives in the house, or I may want the name of the person who owns the house, but may not live there.

I agree that the design is clunky. Maybe I should re-design and move the data around to match the new design.

[walterbyrd]

Would *this* make more sense:

Name (NameID, ...., Owner/Renter, FkAddressID)
Address (AddressID, ...., Local/Other, FkNameID)

I think that would eliminate the redundency.

I think this is somewhat like your "Program - Course" model. Would I need a NameAddress table?

[walterbyrd]

Would I need a NameAddress table? - Nevermind, I would - many-to-many - just like the programs to courses.