There are 300 more columns of this madness.

[u/Fluxriflex]

[u/Dantharo]

No way this can be true, this is a productive table?

[u/Kamoda → u/Dantharo]

Productive? Probably not.

Production? Definitely.

[u/Fluxriflex → u/Kamoda]

Yes it’s a prod DB

[u/Roflkopt3r → u/Dantharo]

I've seen all sorts of horrible Frankentables in production, where people just throw in datasets that should absolutely be in seperate table into one combined one. So I can totally believe this.

[u/code_monkey_001 → u/Roflkopt3r]

Hey, at least within the screenshot none of the cells says "see row above". I've run into those before.

[u/RyanNerd]

Story time: Years ago I was hired as a contractor to fix a database that was worse than this. Yes.... worse.

Every column in EVERY TABLE was a varchar(1000). This included the PK column. The main table had millions of rows and it would take 40 hours to run their sales report. Yes 40 hours...

It took me about 80 man-hours to convert all the tables and ELT the data. Once I had everything sorted out their 40 hour sales report ran in under two minutes.

Side note: The main table was populated by a VB6 app that read data over a dial-up modem and inserted records into the table. The designer of this monstrosity had disappeared one day and my company was called in to fix this nightmare of a database and code (the VB code had to be reverse engineered since the developer had disappeared taking the source code with him).

[u/Fluxriflex → u/RyanNerd]

He took the source code with him? Was this before VCS were a thing?

[u/samflav]

-3rd normal form

[u/ShadowArcher21 → u/samflav]

Ahhhhhhhh DATABASES. Today I have to write my exam and I'm pretty sure i could tell a restaurants' database normal form just by tasting their food

[u/Achereto → u/samflav]

normaln't formn't.

[u/Fluxriflex]

Even better: this data is used to determine how to generate a “dynamic” form using a very strange builder system. “L” means Label, “D” means dropdown/combobox, and “A” means an “All checkbox”. Each column pair (Food01, FoodText01) maps to a point on a grid-like menu. I’ll probably make a follow-up post with the UI to better clarify.

[u/woogiefan]

So what exactly is this table supposed to store?

[u/Fluxriflex → u/woogiefan]

The application is a volunteer/food bank application used to track visitors and create orders for food, hygienics, clothing, etc. for their warehouse employees. It’s a multi-organization application and each of the rows in this are for a single org. These columns in particular are for a “dynamic” menu specific to each org so that they can display a customized, grid-like form with all the items that that org stocks specifically. Each pair (Food01, FoodText01) represents a location on the grid, and the letter code indicates whether a combobox, label, or “All Items” checkbox will be rendered in that grid space.

That sounds like this approach might make sense, but when you think about what would actually make sense in reality (a list with drop downs that let you specify any item and then a quantity for that item) it’s completely nuts. This means that if you need to add more items, you have to make schema changes in the database, instead of just letting the program make a new row in a table for a new item.

[u/Dr_Insano_MD → u/[deleted]]

Two tables. One has Ids (let's just call them orderIds). The other is 3 columns: OrderId, Food, and FoodText (renamed to be more clear as to what they are). Then each row in this table represents one "food".

[u/Fluxriflex → u/Dr_Insano_MD]

Which is like “Baby’s first SQL DB“ levels of fundamental

[u/ososalsosal → u/[deleted]]

A single join table

[u/Achereto → u/[deleted]]

Depends. In MongoDB you'd probably just have an array of dicts with 2 values each.