• Registered: 2020-06-26
  • Posts: 302

Topic: Shared Address Table

Hi All,
I'm starting to get more comfortable with MVD.  However as I progress I find myself wanting to do more.  Probably to be expected.
-
Here's the overall goal of my program so you can see where I'm going.  It will be a RECORD KEEPING program.  There can be one or more USERS.  Each user will be able to keep records about different things, such as CONTACTS, HOUSING history, MEDICAL RECORDS...etc.
-
Currently to begin with, I have 3 tables - USERS - ADDRESSES - CONTACTS (there will eventually be more tables like CONTACTS)
-
There can be multiple USERS and each one can have many addresses.  A Current address and also previous addresses
-
A USER can have many CONTACTS. and a CONTACT can also have one address. Usually their current one.
-
I would like to have only one ADDRESS table where I can store all addresses.  I don't think it sounds like good form to be creating the same address fields in the CONTACTS table, and the others to come later.
-
So I'm thinking ADDRESSES would be a shared table to be used by various categories.
-
It's this shared table concept that I'm having trouble with
-
It's also possible that my design concept is maybe not the best way to go?
-
Any ideas will be appreciated.
-
Thanks in advance. The help I have received from Derek and others on the forum has been fantastic.
-
Thanks, Frank  smile (everyone please stay safe)

2 Reply by derek (edited by derek 2020-07-25 14:21:27)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,681

Re: Shared Address Table

Hi Frank,
Ask 10 people and you'll get 11 different answers - LOL!
If it were me, I'd go with discrete tables for simplicity although I can see why you'd be tempted to use a common address table.
The main reason for holding address data in a separate table would usually be because of a 1:many relationship between the parent entity and the number of addresses being stored against that parent entity.  This is certainly the case between your 'users' and your 'housing history'.  But from what you say, there is just a 1:1 between your 'contacts' and their address and so I wonder if there is much merit in splitting the data across two tables ('contacts' and 'addresses').
That said, it can, of course, be done.
My other concern might be just how much information on a common 'addresses' table actually ends up being common - street, city, zip etc clearly are, but fields like 'date moved in' and 'date moved out' are specific to 'housing history' records and not 'contacts' (of course, the purists would then tell you to put only the truly 'common' fields into the 'addresses' table and hold separate tables for repeating data that is unique to 'housing history',  unique to 'contacts' etc etc).
None of which really answers your question (or even helps!).  And I feel it's only fair to point out, as I'm sure you appreciate, that such database design issues are not unique to MVD and would need clarifying irrespective of what software you were using.
Derek

  • Registered: 2020-06-26
  • Posts: 302

Re: Shared Address Table

Derek,
Many thanks for your reply.  I agree with your thought of "discrete tables".
-
After your recent suggestion to use "pagecontrols" for my different categories I was moving along really nicely.  Everything was working OK.  But as I was adding more 'category' tables, I noticed that I was repeating the address fields in some of them.  So that's when I thought maybe I should have a separate 'address' table.  So I tried it and of course it broke things so I asked for help.  That old saying "if it ain't broke, don't try to fix it" applies here.  I should have left what was working alone.
-
I'll go back to where I was and I should be OK.
-
I really appreciate the help you and the others provide on the forum.
-
Thanks, Frank smile