Data Redundancy Question

I have a User and a Person table. There are scenarios in my App where a Person can exist, without a User associated.

My question is, do you think it is Ok to store the name fields (first, last, other given) on both tables? Technically this creates redundancy, however, from an administrative point of view it makes it much easier to identify who a user is on quick glance…

I’m also include to duplicate other identifiable information such as date of birth, sex, so that (for example) I can differentiate between two John Browns.

Does anyone have thoughts on this? Would it be best to just have a field person (type Person) on the User table? From what I can see there’s not an easy way to traverse through tables via the interface e.g. if I hover on a person ID it has their row in a pop up, or it’s hyperlinked so I can click through…

Thanks !

Hi there, @thethinklab.au… my two cents… if every user is a person but every person is not a user, then I would almost certainly create a thing for every user/person in the Person data type, I would put all of the personally identifiable data in that data type, and I would not duplicate any of that data on the User data type. I would, as you mentioned, have a person field on the User data type that links a user to their person record.

I’m not sure what you mean by traversing through tables via the interface, but I’m pretty sure whatever you are talking about there wouldn’t stop me from configuring the data types the way I suggested above.

Hope this helps.

Best…
Mike

2 Likes

Thanks @mikeloc !

In regards to ‘traverse’, what I mean is jumping between tables/records in the Data > App data tab.

For example, if Users just has email and person, I currently need to manual copy the unique person ID then do a search in the Person table. Having name/DOB in Users would lesson the burden on my part.

This wouldn’t be an issue if I achieve my end-state vision of the app, as it would be unlikely admin would be manually looking at the users table, but would be quite useful during MVP and early stages.

Further expanding on my original question, how about email? If a person is manually entered, and no user account associated, they will have contact details including email, mobile etc. etc.

Say they then sign up and create a user account (I’d have a feature during sign-up for them to ‘claim’ an existing person if there was a match), I’d be in a scenario where User would have an email, Person would have an email. Is this a redundancy issue, and how should I attack this?

I had a feeling you were talking about the App data tab, and to be blunt, there is absolutely no way I would add redundant fields just to make it easier to traverse data in the editor. Also, I’m guessing you know this already, but you can change the primary field for the person field from the unique ID to another field that at least makes it easier to tell at a glance who the person is. If you haven’t done that before, click the Primary fields button above the data type names, and you can change it there.

About the email, you essentially have to have a redundant field on that one because you need an email field on the Person data type in order to match a new user to an existing person. In my opinion, though, that’s not necessarily redundant… it’s simply necessary given the way your app works.

1 Like

Yeh ok, that makes sense - thanks !

I had changed the Primary field in the past, thinking it was just something that would impact the App Data tab, but then I found that whatever I pick essentially serves as the key - which means anything other than the unique ID isn’t really appropriate?

Thanks RE: email… It also occurs to me that a users sign-in email, and their contact email, might not always be the same although I need to do some more research (from a best practice perspective) on whether or not I should enforce this/how I should handle this.

1 Like

Nope, it’s for display purposes only.

Awesome - will try it out !

Agreed with Mike’s advice. I’ll add on to that to say that creating a text field called something like “search combo” that’s a combination of their first name, last name, phone number, etc. helps get around picking just one field as your primary field.

It also makes creating search boxes easier because you can search for all those things without having to use one of those fuzzy search plugins.

You just need to be sure to update it any time one of the other fields is updated with either a regular workflow or a database trigger.

2 Likes