Relationships

[xeb]

I have a layout with several fields including six fields named Code 1, Code 2, Code 3, etc. all with dropdowns that each have six choices the user can select. When the user saves their choices the current structure enters them into a table with a relationship to one of the six columns. I think it should be putting them into a single column identified by the foreign key for normalization reasons. Is that correct? Thanks.

Edited June 29, 2016 by xeb

[comment]

It is difficult to advise without knowing what real-life objects your solution is supposed to track and for what purpose. I also did not understand this part:

44 minutes ago, xeb said:

When the user saves their choices the current structure enters them into a table with a relationship to one of the six columns.

In general, numbered fields are a symptom of poor structure and you should consider using related records instead.

[xeb]

I thought for normalization reasons that data entered like what I have (Code 1, Code 2, Code 3, etc.) was supposed to post to a single column instead of six columns which means lots of blanks at least in my case.

What are related records?

[comment]

6 minutes ago, xeb said:

I thought for normalization reasons that data entered like what I have (Code 1, Code 2, Code 3, etc.) was supposed to post to a single column instead of six columns which means lots of blanks at least in my case.

FileMaker will not perform normalization for you. If you have defined six fields as Code 1, Code 2, ... Code6 then you will have six individual fields, with nothing in common between them.

 

8 minutes ago, xeb said:

What are related records?

http://www.filemaker.com/help/15/fmp/en/#page/FMP_Help%2Frelated-tables-files.html%23

 

[xeb]

I know FileMaker does not do normalization. I have six fields  (Code 1, Code 2, Code 3, etc.)  on my layout some of which are usually blank when data is entered. I was told that having hundreds of blanks in a table is not right and it should be restructured so that when data is entered from any of the six fields it posts to a single column which means there will never be any blanks.

Another question I have is what type of relationship I have. Is it one to many or many to many? It is a database that manages codes and keys. Employee information gets entered and the codes and keys they are issued get entered.

[comment]

2 minutes ago, xeb said:

when data is entered from any of the six fields it posts to a single column which means there will never be any blanks.

I am not aware of any such mechanism. I suggest you use a related table of Codes, and a portal to enter any number of codes the current <object> requires.

As I said earlier, we have no information what this is about, so consider this as a general advice.

[xeb]

Okay, thanks. I am currently using a separate table but not a portal. Is it necessary to use a portal?

[comment]

Just now, xeb said:

Is it necessary to use a portal?

No, but it's convenient, esp if you are a beginner.

[xeb]

Which I am.

I have done a lot of research on what type of relationship I have and cannot figure it out for sure. I think it is one to many. Is it? The reason I ask is because I apparently need a join table if it is many to many. 

Thanks.

[comment]

12 minutes ago, xeb said:

I think it is one to many. Is it?

How would I know? You haven't told us what your solution is about - despite me asking twice already.

[xeb]

 It is a database that manages codes and keys. Employee information gets entered and the codes and keys they are issued get entered.

[OlgerDiekstra]

I assume because you have multiple codes, an employee can be issued with multiple codes? Some have 1, some have 3, some have 6? In which case you'd create a codes table, and link that to your employee. The relation would be between the employee id in the employee table and an employee id (called the foreign key) in the codes table. You make the codes table visible on your employee layout via a portal. This would be a one-to-many relationship (many codes reference one employee).

However, if there are never more than 6 codes, and your employee table isn't expected to grow to millions of records, you could choose to opt for a simpler design and keep the 6 code fields with your employee details. It's a trade off between complexity and storage. But if more codes may be needed in the future, or you want to retain a history of codes, this may not be the best solution.

[comment]

28 minutes ago, OlgerDiekstra said:

you could choose to opt for a simpler design and keep the 6 code fields with your employee details.

No, you should never do that. Just consider how much work it would take to find which employee has code X, for example.

 

31 minutes ago, OlgerDiekstra said:

I assume because you have multiple codes, an employee can be issued with multiple codes? Some have 1, some have 3, some have 6?

You should also ask if the same code can be issued to more than one employee (perhaps not at the same time).

[OlgerDiekstra]

On 01/07/2016 at 4:56 PM, comment said:

No, you should never do that. Just consider how much work it would take to find which employee has code X, for example.

I'm not saying it's the best solution or even a great solution, but it can work. In fact, Filemaker suggests (without saying it out loud) it themselves. Have a look in the starter solutions. The Invoices have a customer table where they have a home and office phone number ("office phone" and "mobile phone") and email ("office email" and "home email") with the customer. Same thing. So they're basically telling their novices (who they expect to start with these starter solutions) that this is a solution.

It doesn't scale well, but there are situations where that is not such a problem.

 

[comment]

I stand by my statement. Numbered fields are a no no. Sure, you can make it "work" somehow. I've seen worse designs work, after a fashion. But I have never seen them work well.

 

7 hours ago, OlgerDiekstra said:

In fact, Filemaker suggests ...

This is not a church and FMI is not its infallible pope. To be fair (to FMI), the analogy is a bit contrived. But it doesn't matter, because I didn't learn database design from FMI.

 

Edited July 4, 2016 by comment