xeb Posted June 29, 2016 Share Posted June 29, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
comment Posted June 29, 2016 Share Posted June 29, 2016 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 More sharing options...
xeb Posted June 29, 2016 Author Share Posted June 29, 2016 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 More sharing options...
comment Posted June 29, 2016 Share Posted June 29, 2016 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 More sharing options...
xeb Posted June 29, 2016 Author Share Posted June 29, 2016 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 More sharing options...
comment Posted June 29, 2016 Share Posted June 29, 2016 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 More sharing options...
xeb Posted June 29, 2016 Author Share Posted June 29, 2016 Okay, thanks. I am currently using a separate table but not a portal. Is it necessary to use a portal? Link to comment Share on other sites More sharing options...
comment Posted June 29, 2016 Share Posted June 29, 2016 Just now, xeb said: Is it necessary to use a portal? No, but it's convenient, esp if you are a beginner. Link to comment Share on other sites More sharing options...
xeb Posted June 29, 2016 Author Share Posted June 29, 2016 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 More sharing options...
comment Posted June 29, 2016 Share Posted June 29, 2016 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. Link to comment Share on other sites More sharing options...
xeb Posted June 29, 2016 Author Share Posted June 29, 2016 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 More sharing options...
OlgerDiekstra Posted July 1, 2016 Share Posted July 1, 2016 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 More sharing options...
comment Posted July 1, 2016 Share Posted July 1, 2016 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 More sharing options...
OlgerDiekstra Posted July 3, 2016 Share Posted July 3, 2016 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 More sharing options...
comment Posted July 4, 2016 Share Posted July 4, 2016 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2854 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 accountSign in
Already have an account? Sign in here.
Sign In Now