Table or field in table? (1 Viewer)

[Megacitizen]

I'm working on a DB that will primarily be used as a Data Entry form for recording the actions and their results, of various teams in various regions of the same city. Without going into too much detail - it's already mentioned in other threads on this forum - I currently have a small number (3) of tables to build on. These are t_Bases (Bases sending teams) t_Teams (teams themselves) and t_Regions (regions receiving teams).

My query is this - I have provisionally set up a 4th table, t_Nations because we have numerous Bases sending teams from the same countries, eg Australia, UK and USA each have sent or are sending more than one team to the city we are working in (currently London, other cities to be added in the future). I've related it to the table t_Bases using NationsID as an FK in t_Bases. Do I need this table or can I get away with just entering the info into a field within the Bases table?

I realise that much of the info input by our users will be repeating information, especially where a base sends more than one team and more than one base is located in the same country. Our reports are strictly on a team-by-team basis ie each team will have it's own record on our DB.

 

[VilaRestal]

Definitely keep the table.

If you only want the user to be entering one of three values then a table with those values is the obvious reference for the database to check their entry against.

In other words you can use a combobox for the nation field that looks up the nation table and limits to list and then there's no danger of typos or people putting a phone number in there.

Without forcing validation in these ways you'll get records that don't appear in queries when they should - that slip through the net. For organisations that do important work for clients (like almost all organisations I guess) that can be very bad for the client. In extreme cases, lives can depend on it.

And how better to force a valid nation entry than by comparing to a table of valid nations?

 

[Megacitizen]

Thanks for your reply, VR, much help.

My next question would be:- I'm going to be needing to expand the DB shortly to include records of the various dates of each team's state of application, from initial enquiry through to Thankyou Letter being sent post-participation. This would be indicated by checkboxes and Date/Time fields for each stage. We also need to record the actual dates they are in the city in question and a week-by-week breakdown of their recordable activities whilst there.

As we intend our forms to be a progess-style record ie we populate fields as and when we receive the relevant information, certain dates (namely dates arriving and departing the city in question) may be entered as future events, whilst all other dates would be post event records. Would I need to use BeforeEvent macros to ensure relevant fields may remain unpopulated until required?

What fields can be added to existing tables (and which tables) and what fields, if any, would require new tables?

 

[Galaxiom]

The events should be stored in a related table with fields to indicate the EventType, when it occurs and the key of the record in the main table it is related to.

There will also be an EventType table to hold the names of the events. Only the EventTypeID is stored in the Event table and the name of the event looked up as required on the form.

You don't want to be adding new fields when new types of events occur. Just add a new EventType record to that table and start using its ID in the main table without changing the structure at all. You can always tell the structure is wrong when you need to add new fields to a table as part of normal operation.

 

[Megacitizen]

The events should be stored in a related table with fields to indicate the EventType, when it occurs and the key of the record in the main table it is related to.

There will also be an EventType table to hold the names of the events. Only the EventTypeID is stored in the Event table and the name of the event looked up as required on the form.

You don't want to be adding new fields when new types of events occur. Just add a new EventType record to that table and start using its ID in the main table without changing the structure at all. You can always tell the structure is wrong when you need to add new fields to a table as part of normal operation.

Thanks. I had an idea that Events would require new tables. We're actually recording a set number of defined activites over a set amount of weeks and the total number of times each event happens each week, whether the action actually happens or not (if not, the value in the corresponding field would be zero for that week). Our form, at this stage, would therefore be a sort of grid, rows for actions and columns for weeks, intersections showing a value from 0-XX. I'm thinking of presenting that section on a subform that's permanently visible on each page.

Also, from the info you HAVEN'T given, can I presume that my Dates queries, regarding various stages of application, can be added to the existing t_Teams table?

 

[Galaxiom]

Without seeing more detail it is a bit hard for anyone to fully understand what you are trying to do. Perhaps post a sample of your proposed table structure or even an image of the relationship diagram.

So far it sounds to me that all the Events records should be held against EventDates, EventTime, LocationID and TeamID.

Don't be tempted to put the weeks as fields in the tables. The separation into weeks would happen in the queries. It is far easier to pull apart the data into weeks than it is to try and query it all back together again from separate fields.

Even though you might think there is a set number of events the users will be asking for more to be added almost as soon as you get going. They always do and that is the general philosophy you should use anyway even if you are sure nothing will be added.

Anything that involves putting the same kind of data into different fields or tables always suggests inappropriate structure. That separation should happen as value in a field to indicate the different aspect of the data, not as an underlying structural separation.

 

[Megacitizen]

"Even though you might think there is a set number of events the users will be asking for more to be added almost as soon as you get going. They always do and that is the general philosophy you should use anyway even if you are sure nothing will be added."

Without going into too much detail about what each event does (Actions might have been a better word to use) the list we work from has the following criteria, Salvations, Healings, Prayed for, Evangelisms, Nos receiving teaching, Teachings (Hrs), Intercessions (Hrs), Bibles given out, Other Evangelistic Materials, HealthCare, Mercy Ministries, Church Mobilisations and 2nd Generation Salvations. We have never collected information on any other actions than those listed in all the 50+ years our Mission Organisation has been operating, so there will be no need to add to that list. But thankyou for your suggestion anyway.

"Don't be tempted to put the weeks as fields in the tables. The separation into weeks would happen in the queries. It is far easier to pull apart the data into weeks than it is to try and query it all back together again from separate fields."

If you are suggesting we run our occurrances as queries, we need to be able to display the results of the query on the report so that when a viewer brings up a Team Report, the figures are already there. In effect, we would want to be able to run the query at the input stage and display the results permanently on the page, rather than run the query each time we open that team's page. It's a bit like a seperate piece of paper pinned to the main Report Card. On it, we still want to record ALL occurrences, even if for that Action, it's ZEROs across the board.

 

[Megacitizen]

Next question - I have created another table, which we intend to use as the basis for a hidden subform which will display, on request only, basic details of individual team members ie name and nationality. I have linked this table to the t_Teams table using TeamID as the PK and FK where necessary.

I have NOT linked the Nationality field in the t_TeamMembers table with the t_Nations table as for our purposes, Base nationalities and Individual nationalities don't necessarily corelate. EG a Canadian individual could come from a North Korean Base, and a Jordanian could be with a team from an Israeli base (Politically unlikely in the real world, I know, but not impossible in our circles).

Is everything still on the right track here? Or do I need to consider other relational links between tables?