Large Database Structure Question

[PrincessApril]

Hmm, so when I create the combo box on the form, it asks me where to store the value selected from the lookup table, so I thought that I would need a table field to store the selection?



And thank you for the encouragement!

[GinaWhipp]

You will be storing the PK not the value which is all you should be storing. You can see the value in a combo box because you will have two column the first being 0" length. You do need or require the actual values in the table.

[PrincessApril]

Okay, thanks so much Gina. I think I can envision that. I can see what you mean about the "weeds," as it seems like there will be a huge network of interconnected tables.

So just to clarify--I am not setting any lookup options within the tables themselves; rather, I will be doing that all through the form controls, correct? (and I suppose that will be a good place for me to end this thread! )

[GinaWhipp]

That is correct, no look-up's at the table level. Everything happens via the Form or Report level.

End if you like, we will all still be here should you have additional questions.

[PrincessApril]

That is correct, no look-up's at the table level. Everything happens via the Form or Report level.

End if you like, we will all still be here should you have additional questions.

Thank you so much!

[PrincessApril]

A related question that just occurred to me is how to handle values that would come from a lookup table where only one value is possible (this thread has thus far dealt with fields with multiple values possible). For example, in the first attached image (couldn't delete second image sorry), I have a table relating to the client's primary phone number (I'll have a separate table for alternate number, because either number could change at different times). Only one value would be possible for the fourth row (phone type).

• I'm using an FK from ClientID because the phone number might change over time.
• So then we come to the fourth row. I have a lookup table for the phone type (personal cell, VoIP, home landline, work landline, etc.) Am I doing this right by just having phone type as short text in my table, then in the form I will use a listbox to draw from the lookup table? Or do I have to use a FK and combobox for this too like with the multiple values earlier in this thread?

[GinaWhipp]

Hmm, let me tell you what I do...


So, I have a tblEntityCommunications in which which is ecCommunicationTypeID. This can be a Primary Number, eMail address, Website, etc. (I also added an Opt Out check box in case the person does not want to you to use the type of communication. I think something like this might be more beneficial because it is *fluid* and allows for your own custom Communication Types, i.e. Primary NUmber.

Does that help?

[PrincessApril]

Thanks Gina. I agree that that would be more adaptive overall.

I probably have to keep similar to the current structure because I am following a paper form, and we have to have certain data entered electronically as required by our funders.

So, considering that, I'm still wondering about my fourth row. Should I handle it with a FK just like with other tables in this discussion (e.g., race), or is the situation different given that only one value is possible from that lookup? From the discussion in this thread, it seemed like the use of a ID with FK was only necessary for lookups with multiple values? That's why I couldn't decide how to handle the fourth row (right now just short text and I plan to do lookup in the form).

[PrincessApril]

So I will have tables for primary and secondary phone number with the same fields you see in the attached image. I'll also have a smaller table for email address. I have a separate table for mailing address. And finally I will have one sort of stranded field, which will be client's preferred method of contact, which I suppose I'll just make it's own table (again that last table would raise the same question of how to handle a field whose data will ultimately come from a form listbox where only one value from the lookup table is possible). I'm not sure if I'm asking the question clearly though.

[GinaWhipp]

Forget about the paper form. You can use subreports, code and queries to make it look anyway you want. You can also use automation to import to the different tables. So no worries there, we just need to make sure you are storing it properly.

You ALWAYS need a PK no matter what. And it is always better to use a NUMBER as ultimately they take up less space and you want to maximize space when storing data. Remember, you will get the value (SHORT TEXT) when it's time to do a report.

You do not need another table for eMail's. You can use the same table just base the look-up on the Communication type ID you assigned to the eMail Address. Because I am thinking people might have more than one eMail Address.

[PrincessApril]

That's true--I have to forget that mentality sometimes and remember I can manipulate as needed in the form.

Attached is what I have so far (a bit different than yours based on needs). I can show or hide certain fields based on the selection of the type of contact, such as only showing the "okay to leave voicemail" field when a phone type is chosen.

I'm actually not sure if I should call DateFrom something else, like DateLastVerified or something, because let's say the phone number has been active for 2 years--they might not remember the date when it was first active but the relevant part is whether it is still active?

[PrincessApril]

And here is my alternate contact table (it's got enough different fields I think it's worth having a separate table for these)

[GinaWhipp]

You were going great until you created a separate table for Contacts. No need just add a field to the first table to indicate Contact Type which could be a Yes\No and if Yes then a Client the default being NO indicating it's a Contact. Just add the extra fields to the table, you don't have to show all of them on the Form.

Also I see you have cc in some and ccc in others. Is there a reason the prefix changes?

[PrincessApril]

You were going great until you created a separate table for Contacts. No need just add a field to the first table to indicate Contact Type which could be a Yes\No and if Yes then a Client the default being NO indicating it's a Contact. Just add the extra fields to the table, you don't have to show all of them on the Form.

I see...that makes sense. I couldn't make up my mind, so you helped me decide!

Also I see you have cc in some and ccc in others. Is there a reason the prefix changes?

cc stands for Client Contact (tblClientContact). The extra c is only to indicate that the FK came from tblClients (and, similarly, in the next row, the extra ct is because the FK comes from tblCommunicationType). I thought I had seen a couple people using that convention earlier in the thread, so I just copied it. It was a bit much for me to process at first, but I'm getting the hang of it. Do you suggest another way to organize the prefixes?

[GinaWhipp]

Oh okay. And nope, not suggesting another way you need to do what works for YOU, just be consistent. Everyone has their own naming convention which are sometimes identical but not always. I just wanted to know so when looking at your field names I don't think you made a mistake and typed an extra character.