ACCESS FEED: Re: [MS_AccessPros] Table Design Question

Sabtu, 24 Oktober 2015

Re: [MS_AccessPros] Table Design Question

Actually, I had not thought of having a from and to date on a second table to connect the owner with the unit(s). That does give me history. A very good point.

On Oct 24, 2015, at 1:11 PM, Stephen Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok so what you call crosswalk, I call junction. It still might be a valid option though. Although it is a little more complex, even if there are only ever one owner (at a time), the relationship between the 2 does have its own attributes (ie, start and end dates) that may need tracking , and that may rise it to the level of needing a table. Also, if your solution needs a way to express a second owner (to borrow from a car commercial) and not lose the prior history, this would point to a 3^rdtable as well. Just some food for thought, hth.
-Steve

From: MS_Access_Professionals@yahoogroups.com[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, October 23, 2015 11:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Table Design Question


Thanks By crosswalk, I meant to create a third table that had the ID for the Unit with the corresponding Owner ID. In my design, I will only have one owner to many units. We only allow one contact name and mailing address so I could have John and Mary Smith and one with just John Smith but that would only be 2 records. Thanks for the help.

On Fri, Oct 23, 2015 at 11:06 PM, Stephen Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Jan:
I am not sure what you mean by "Crosswalk" … does that mean an OwnerID in Units and UnitID in Owners? I wouldn't recommend that.

If one-and-only Owner has one-or-more Units, like Invoices have InvoiceDetails, then yes, a one-to-many relationship, with the OwnerID as an FK in the Unit table makes sense.

However, if there is a possibility of partnerships, or multiple owners (married/separated couples?), then you should consider a junction table to facilitate a many-to-many relationship. So you would have a OwnerUnit table, with both an OwnerID and a UnitID (and probably start and end dates, but I am leaping ahead there.)

Remember in db design context "many" means the possibility of there being more than one, on that side of relationship.

Hth,
Steve Conklin

From: MS_Access_Professionals@yahoogroups.com[mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, October 23, 2015 10:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Table Design Question


I am setting up a database for my homeowners' association. I plan on starting with two tables. Units and Owners. We still have a fair number of undeveloped lots so some of the owners will own multiple lots.

I can ether create an OwnerID field in the Units table or do a crosswalk. I am leaning toward putting a field in the Unit record but wondered if this is the best way. Probably no difference but I thought I would ask.

Thanks