Jump to content

Relationships


xeb
 Share

This topic is 1853 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by xeb
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 1853 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.