Level 22
Personally I took the approach of:
- primary key
- relationships
- model attributes
- extra model attributes from external packages (example stripe)
- something else
- date time created updated
Nov 6, 2023
9
Level 1
Table Columns Order Best Practice?
I have several tables with 60 to 100 columns in them.
The columns were originally ordered, in the migrations, relative to how the data would be structured in the form; except for the id primaryKey being first and the withTimestamps being last.
As some of these tables have grown, and our dev team as well, the fields are being added to the end of the tables, honestly making them look messy.
Is there a best practice out there that we should be following? Should we go with alphanumeric except for those mentioned above? Does it really not matter?
Just curious how everyone is managing this.
Level 1
@wafto I like this style. That is similar to what I do now. Perhaps in each of those sections I will then put in alphanumeric order. Thanks for the input.
1 like
Level 80
@dmhall0 Sounds like you need to fix the problem and not the symptoms. 60 to 100 columns for a table is a massive number. So I feel there’s probably improvements to be made on how you’re modelling things.
So worry more about how to accurately model your data and store it relationally, rather than how to order a massive amount of columns you shouldn’t have in the first place.
Level 1
@martinbean Thanks for the input. If I could figure out how to simplify these tables, and their forms, I would. They are large forms used by medical providers and they like a lot of checkboxes and radio buttons.
Level 122
@dmhall0 forms has nothing to do with database table structure.
Hey, but as you brought it up, you have forms with 60 to 100 fields to be completed ????
Level 80
@dmhall0 Yeah, as @snapey says, a database table isn’t meant to have a one-to-one mapping with a form on a website. A database table (in a relational database such as MySQL) is meant to define a schema for some entity. So you would model your entities based on the business domain, but it doesn’t mean the form is going to have exactly the same fields, in exactly the same order, with exactly the same names.
Forms will hide internal business processes and names because they’re for a different audience, especially in a field such as medicine where there’s very specific language practitioners use that a layperson is not going to understand or use in everyday life.
Level 1
@martinbean @snapey Thank you both for the feedback. Trust me, I am all for simplifying the form and/or table as much as possible. I'll give you an example. One form has 22 questions needing a 1-10 rating, 23 questions with normal / abnormal response, and another 12 questions with yes / no response and an optional note field for each, plus many more questions. I need to very quickly and easily compare answers to each of these questions across entries for data analytics and predictive outcomes.
If there is a better way to build the form / table, I am all ears. Thanks again.
Level 14
@dmhall0 I'm more curious to hear how this massive form is handled from UI/UX. Does it have steps? Can I submit half of it? Can I come back and finish it latter/ the next day?
As for the DB structure a nice starting step would be to have the submissions table with some meta data about the whole form and the answers table with the actual responses in a one to many relation. This way different forms can have different questions without ending up with null columns on your submissions table.
Level 74
@dmhall0 I would suggest giving this a read, it talks about how I do with my tables, and which questions I ask for each column, and there is nothing wrong using one-to-one relations.
Please or to participate in this conversation.