Marco,
My structure is way more complex than I outlined here is the master table: RegisterOfMbrs 66 494 Members initiated or alliliated Here is the full list of subordinate tables: MbrAcademicInfo 5 507 Academic degrees awarded information MbrAltEMailInfo 4 62 Alternative Email Address MbrComments 3 85 Additional Comments for Member MbrCtbHistory 12 21 Member Contribution History MbrCurEMailInfo 4 366 Member current Email addresses MbrCurHomeAdr 13 452 Home Address information MbrCurHomeAdrRev 11 437 Home Address information verified by USPS search MbrCurWorkAdr 17 353 Member Current Work Address MbrCurWorkAdrRev 11 248 Work Address information MbrFathersInfo 14 243 Information on Members Father MbrPrvEmailInfo 4 333 Audit record of previous Email addresses MbrPrvHomeAdr 13 1083 Audit prev MbrHomeAddress. Deleted adr or Dead Mr MbrPrvWorkAdr 16 553 Audit prev MbrWorkAddress. Deleted adr or Dead Bro MbrRelatives 13 96 Relatives of Brothers who are also Theta Xi Members There is a status code on the RegisterofMbrs which control some of the subordinate tables mbrstatus count (*) -------- ---------- Actv 12 Alum 415 Asoc 9 Dead 33 LclA 12 Lost 8 NoCn 1 UndG 4 Once a person is added to the Register and becomes an initiated brother that record is never deleted. For address information I split them into current and previous because I receive address and email information form many sources. I can receive an update from the National Headquarters, the University Alumni Association, the member himself, internet research. In many cases my source information does not have a change date associated with the information. In many cases I have a more recent address than the source information. The Previous tables allow me to determine which is the most current. . You will note the various number of records in the subordinate tables. Part of the problems is that we are still reconstruction information that was lost as the result of Hurricane Katrina. For example MbrFathersInfo should have a 1 to 1 ration with the register. As you can see we are still accumulating this information. On the other hand there will always be a one to many link between the register and MbrAcademicInfo. Undergraduate members usuall do not have a University/college degree. Some members never graduate college, Some members have an Bachelor degree only, and others have one or more advanced degrees. Only one record is allowed in the Current Address and EMail tables by making the MbrNumber a PRIMARY KEY in the subordinate table with a FOREIGN KEY relationship to the RegisterofMbrs. Reporting is driven by views many of which involve a complex set of nested JOINS. For example "FROM ((((((RegisterOfMbrs R LEFT OUTER + JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber) J1 LEFT OUTER + JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 LEFT OUTER + JOIN MbrCurEmailInfo E ON J2.MbrNumber=E.MbrNumber) J3 LEFT OUTER + JOIN MbrAltEmailInfo A ON J3.MbrNumber=A.MbrNumber) J4 LEFT OUTER + JOIN AcademicAsList B ON J4.MbrNumber=B.MbrNumber) J5 INNER + JOIN DirHighSchools S ON J5.HighSchoolNbr=S.HighSchoolNbr) J6 LEFT OUTER + JOIN MbrFathersInfo D ON J6.MbrNumber = D.MbrNumber " Another example: "FROM ((((RegisterOfMbrs R LEFT OUTER + JOIN MbrCurHomeAdr H ON R.MbrNumber=H.MbrNumber AND DeathYear IS NULL + AND AcademicRecCnt<>0 AND UNODegreesCnt<>0) J1 LEFT OUTER + JOIN MbrCurWorkAdr W ON J1.MbrNumber=W.MbrNumber) J2 LEFT OUTER + JOIN AcademicAsList A ON J2.MbrNumber=A.MbrNumber) J3 LEFT OUTER + JOIN DirHighSchools S ON J3.HighSchoolNbr=S.HighSchoolNbr) J4 LEFT OUTER + JOIN MbrFathersInfo D ON J4.MbrNumber = D.MbrNumber " If you begin to introduce Active/Inactive flags into the subordinate tables the JOINS could become way more complex. In addition there is the problem of maintaining the "Yes/No" fields. I use a complex set of TRIGGERS to maintain the previous address tables. When I make a change to data in the current table the save of those records are analyzed by a BEFORE UPDATE trigger and if appropriate a record of the old information is inserted into the previous address table. Also, record count and audit date/time is updated in the RegisterofMbrs. I know this was a somewhat long winded answer to your simple question. The reality is that there is no right or wrong answer.Each database design depends on current requirements and the inevitable "oh by the way we would like to do..." changes. For example the EMail information was part of the MbrCurHomeAdr table until I split it off into its own table. Change was prompted by a decrease in the frequency of our U.S. Post Office mailings and the increase in use of EMails. Remember, that this discussion Was triggered by Bill Stacy's questions about database restructuring. In many cases as was Bill's, Database structures often start out mimicking spreadsheets with one massive table Name columns, address columns, etc all in one recored. The come the inevitable I need multiple addressees or I need additional phone numbers dilemma on how to change the table. I have been through four major redesigns of the American Celiac Society's Address Database (not detailed here) along with my Fraternity Database part of which is detailed here. Both are in the process to undergoing anothe major redesign as I move from the DOS version to the Windows version. I need some major column name changes as for example I have used "FirstName" as a column name in multiple tables. Some of which were not initially conceived of ever being joined fro inquiry or reporting purposes. Surprise! Surprise! That is now needed and causes problems in the Form and Report table linking sections. I need to rename some columns that are spread thru views, stored procedures, multiple form and multiple reports. No easy task. .Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 >________________________________ > From: Marco Groeneveld <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Wednesday, February 22, 2012 2:11 AM >Subject: [RBASE-L] - Re: Too relational? > > > >Hi, > >I was just wondering why you don't use 1 Master + 1 slave and in the slave add a field: Active Yes/No to show/use the Current Record ? > >Marco >----- Original Message ----- >>From: James Bentley >>To: RBASE-L Mailing List >>Sent: Wednesday, February 22, 2012 7:56 AM >>Subject: [RBASE-L] - Re: Too relational? >> >> >>Bill, >> >> >>In my main membership database I use the following structure: >> >> >>RegisterofMbrs - master table with Name(s), and other key information. >>Primary key is MbrNumber >> Trigger Maintained count of records in slave >>tables >> Audit information on changes to slave tables >> >>MbrCurHomeInfo - slave table. Current Address, Phone Numbers. Source codes. >>MbrNumber Foreign Key ref to RegisterofMbrs >> >>MbrPrvHomeInfo - slave table. Previous Address history. MbrNumber Foreign Key >>ref to RegisterofMbrs. Updated by Trigger >>MbrCurWorkInfo - slave table. same as above but for work address items >>MbrPrvWorkInfo - slave table same ad above but for work history items >>MbrCurEMailInfo - slave table. current email address >>MbrPrvEMailInfo - slave table previous email address history >> >> >>Use of VIEWS employing nested OUTER JOINS allow you to maintain variable >>number of records in slave tables. >>My system makes extensive us of BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE >>triggers to maintain recored counts, Audit action, date, time codes, >>VERFICATIONS REOUTINES. >> >> >>Jim Bentley >>American Celiac Society >>[email protected] >>tel: 1-504-737-3293 >> >> >>>________________________________ >>> From: Albert Berry <[email protected]> >>>To: RBASE-L Mailing List <[email protected]> >>>Sent: Tuesday, February 21, 2012 10:51 PM >>>Subject: [RBASE-L] - Re: Too relational? >>> >>>Yep, I would have a Persons master table, a PersonStatus table and a PersonAddress table containing address(es) with a switch to indicate Active or Inactive status, and follow that along. If a person lives in Hawaii for the winter and in British Columbia in the summer, both addresses would be in the PersonAddresses table with one only allowed to be active at any given time. Saves a lot of keying. You could go as far as PersonPhones if you really need to, adding a phonetype column to indicate cell, land, satellite, fax ... >>> >>>Albert >>> >>>On 21/02/2012 6:04 PM, William Stacy wrote: >>>> In my efforts to "normalize" my database, I'm even finding the need to split off postal addresses, telephone numbers, etc into separate tables. Presently all addresses and phones etc reside in a person table and/or in a family table. I haven't done this yet, but am thinking about it. In the end, my "family" table may end up only having about 3 or 4 columns, which identify the person's mom and dad for blood relative connections, and maybe a responsible person (bill-to and family addressee) connections. Can't think of much else that is needed there. >>>> >>>> My reason is this: many people have alternate addresses, some more than 2 such as office addresses, PO Boxes, military addresses, vacation homes, bill-to addresses, girl-friend or sugar-daddy addresses and so on. Same thing is true of phone numbers. Really, these are almost one-to-many items. In the reverse fashion from what you might think. Really, a single street address can have many people associated with it, and the address itself really doesn't change, only the residents do. Same thing is true of phone numbers. My cell num is unique, but after I give it up, someone else will eventually get it. >>>> >>>> Anyone construct tables thusly? >>>> >>>> -- William Stacy, O.D. >>>> >>>> Please visit my website by clicking on : >>>> >>>> http://www.folsomeye.net >>>> >>>> >>>> >>> >>> >>> >>> >>> > >
