NEWBIE - CONFIGURING ONE-TO-ONE AND AUTO UPDATING
AFFILIATED GMRS REPEATERS USERS
DATABASE DESIGN NOTES FOR NET CHECK-INS
September, 2023
Software: Ninox – v 3.9.3 downloaded free from Apple App Store, single computer only.
Data only on local hard drive for simple start, but later can be shared in Ninox Cloud
for future use by more than one member. Ninox Cloud $ 10.70/mo. 20% discount?
TABLES & FIELDS
3 Tables: Repeater Users Roster, Radio Net Operators & Radio Net Check Ins. Red indicates Requiredfield. Green indicates default. Italics and indent indicate linked tables/fields.
Repeater Users Roster -- Fields/Columns:
Tab – Users Roster Color Green
ID
First Name – Text
Middle Name – text
Last Name – Text
Nick Name - Text
FCC Call Sign - Text 8 characters
Exp. Date – Date
Unit ID - Text
Affil Date – Date
Affil Type – Choice: Regular|Auxiliary|Probationary|Applicant|Auth. Pers. User|Drop
Type Date -- Date
Address line 1 – Text
Address line 2 – Text
Str Name – Text
City -- Text
State – Text (Choice Drop Down IL IND WIS MICH)
Zip Code – Number 5 + 4 digits
Primary E-Mail – Email
Main Phone – Phone
2nd E-Mail – Email
2nd Phone -- Phone
ID Picture -- Image
Radio Net Operators
Radio Net Check Ins
Radio Net Operators -- Fields/Columns:
Tab – Net Operators Color Blue
Id
Repeater Users Roster
Type Date
Status – Choice: Active|Inactive (radio buttons)
Name—Text
City - Text
FCC Call Sign
Radio Net Check Ins -- Fields/Columns:
Tab Net Check Ins Color Purple
Id
Date of Net – Date
Repeater Users Roster
Affil Date
FCC Call Sign
Name
City
Channel – Choice: .675|.650|.700|Web|Relay (switch default)
Radio Type – Choice: Control|Mobile|Portable (radio buttons)
Additional – Choice: Early|Late|New (radio buttons)
Remarks- Text
I am band new to Ninox and databases. I’ve read most parts of the Ninox Documentation and watched quite a few of the Ninox and NinoxUS tutorials so far. I have 2 questions at this point of my efforts. Radio Net Operators table is a subset of Repeater Users Roster. Only some Users are Operators. But all Operatorsare Users. And all Operators have additional data fields.
Questions: 1) Relationship from Repeater Users Roster to Radio Net Operators is not one-to-many, it is one-to-one. How do I configure this? I saw something somewhere saying a 3rd table in between is necessary to tie each of the 2 table records together, but now I can’t find this again.
2) When Roster data is edited/changed in Users the linked columns in Operators should be updated at the same time. Does this happen as soon as the tables are linked? Or, should I make the linked Roster table to Operators a Composition to then make Operators a child of Roster in order to make edits in Rosterautomatically appear in the columns linked into Operators? Or does this require trigger after update, and, if so, does this have to be done for each linked in field? How do I make Operators stay up to date automatically when Users is edited?
Any responses/suggestions would be most appreciated!
12 replies
-
Hi Randy - welcome to Ninox. It would probably be easiest to work this out if you uploaded a archive of your database.
Best
Simon
-
Hi Randy - it looks to me that you don't need a table for "Radio Net Operators" - you can simply have a field (probably a multiple choice field) in the "Repeater users" to mark as Operator/Call. You can have a separate view in the Repeater users table to show just this.
-
Welcome to Ninox.
As said, you probably don't need another table since Radio Net Operators. Like you said, the two tables would have a 1:1 relationship which makes me wonder why create a separate table.
You need a separate table when you find yourself tracking multiple instance of the same data. For example, a user could have multiple email or phone number you might be tempted to create email1 and email2 or home phone, work phone, mobile. Instead you could create a new child table called Contacts and that table can track all of the ways you can contact the person.
said:
When Roster data is edited/changed in Users the linked columns in Operators should be updated at the same time. Does this happen as soon as the tables are linked?It depends on how you setup your tables. In the following setup we have TableA and TableB and they are linked with a reference field in TableB.
If you create regular fields in TableB (text, number, date, etc). Those fields do nothing but hold data.
You can create formula fields in TableB that look through the link to get data in TableA, so whenever data is changed in TableA it will show up in TableB.
or
You can create Triggers (at the table or field level) or buttons in TableA that will then write the data to fields in TableB.
-
I can't quite see the problem:
"However, of the perhaps 250 Users, only about 50 will also be Net Operators thus needing additional fields. So that's a lot of wasted empty fields for most Users who don't need them."
What further data do you need to add to Operators? It would be easy enough place those fields in a tab that's hidden in non-operators. You'll either have fields you don't need, or an empty table relationship. I've uploaded an illustration, with a tick box to mark 'operator'.
I think this "I saw a graphic and some text about configuring one-to-one relationships with an interfacing table in between. Each record appeared once on each side of this table." may be with regard to how to set up a many-many relationship, where the standard in Ninox is to add a linking table.
-
There isn't a 1:1 relationship available to Ninox natively. Use a 1:many with the many side being the optional additional fields. You could write a script to prevent additional records from being created on the many side.
I would favour the additional fields being included in the main table with all the fields being collected together under a tab that is conditionally shown or hidden depending on the status of a flag. The days have long gone when you had to worry about the empty fields taking up space. Good structure with yield far more benefit.
Regards John
Content aside
- 7 mths agoLast active
- 12Replies
- 118Views
-
4
Following