If you don't have certain tables, can you provide some clue as to what causes you to have hundreds and why they are growing. Do any of your tables have similar structures? Help us understand what you have created.
Hi Duane,
I have so many tables because my database does many, many things. It has two major domains: personal finance and health. Finance addresses income and expenditures. The expense domains include utilities, insurance, groceries, home repairs, etc. The grocery domain considers questions such as how much a family spends in a month at Walmart versus Safeway versus King Soopers? The Utilities domain considers the amount that a family spends on water, electricity, and gas. In addition to expenses, the utilities domain also considers the consumption. How many gallons of gas did the family use this month? How many kilowatts of electricity did the family use? For these, why did the amount change from month to another? Did the family water the lawn more in June than in December? Why? For phone costs, which service is more expensive? Is it T-Mobile or AT&T? Then should the family consolidate and use only one telco?
In the health domain, the database seeks to help family members understand the diseases that each person has. Do they eat bad foods regularly? What are bad foods according to dieticians? What do family members' medical test results show about their next medical issue? Will a person have kidney problems if he doesn't stop drinking alcohol? Which person needs to do more exercise? Most tables in the health domain have a field for each family member.
The database addresses each person's health separately. These are main form/subform constructs. The subforms are continuous forms. The subforms store data for each event/expense with a person's name, health issue(s), event dates, and cost to treat. Aggregate information will tell folks the frequency, total cost, and actions taken to prevent a problem from occurring again or worsening.
The database will track ER visits month by month. I have one family member who visits the ER every week. The staff has a room set aside for him. They probably have a GPS tracker in his car to tell them when he is on his way
In recent weeks, food recalls have been front and center. What foods were recalled yesterday? Which recalls were Class 1? Where in the country was the product shipped? How many people died from the product? Sometimes I think that I should stop eating food
Because I initially built the database for personal finance, almost every table in the health domain has a field for cost. The database tracks the cost of doctors' visits, prescription meds, health equipment, and over-the-counter meds. It will aggregate medical costs over time, by doctor, and by pharmacist.
The database uses the five W's for everything. I add new fields to tables when I discover that I forgot a question to address. That happens from time to time because I am always reading books, magazine articles, or website posts about finance and health.
I hope this helps, Duane. As I mentioned before, I am not a financial consultant or a physician. I read a little, and then I build a capability into the database. I am sure it has lots of flaws.
I am also sure that no one has ever developed a database like mine. How could they? No one knows my family's medical history, where they live, and what they eat. They don't know which individuals are on medicare, and which ones are on a company health plan.
Most developers have built applications for use by extroverts, and some have built them for introverts. But I think that most developers do not consider personality when they create a database. For them, normalization and technical issues are most important. Being introduced to MBTI in the federal government, I consider personality first and foremost. I consider reports to be more important than forms. Reports are how I share information with others. I use forms to enter data in the system. The forms will show the person what data is missing so he/she can make a decision about what to do next. You won't see spreadsheet-like tables in my database. The forms show context data for each person, disease, store, expense, etc.
The medical history of my family goes back generations. Members live from Florida to California. Some members have strict diets and others eat every that they see. Some are Black, some are White, some are Hispanic, and some are Asian. There are men and women. All of these differences need to be built into this database.
Thanks for helping me think through all of this. I haven't created a navigation diagram yet, I am afraid that Access may not be able to show all the linkages. My copy of Visio is ancient.