multi column database table

rich1812

Hello, I haven't had a situation where I need to use a very large table. But just out of curiosity and as an academic exercise, I am wondering how to design a table if it calls for a something like, let's say over 50 columns, should the table be broken up into serval smaller
tables, what is a proper way to do this?

NogDog

Generally speaking, that smells like something that probably would be better by modeled by multiple tables. However, it's possible it makes sense, if each of those 50 columns represents a discrete property of the thing being modeled by each row, as opposed to multiple instances of the same "thing." More often than not, that's not the case, but -- well, we'd probably need some more details before passing final judgement.

jedaisoul

In general. the table structure should follow the data structure. So, no, you should not have multiple tables with a one-to-one relationship to each other. Also, I can't see what you would achieve by it anyway???

root

It depends on the data you are storing, how often it would change.

50 columns in a table is a bit excessive and would say that without seeing a typical table element and the data, its hard to say because thers so many ways of dealing with storage of the data.

NogDog

jedaisoul;1492881 wrote:

In general. the table structure should follow the data structure. So, no, you should not have multiple tables with a one-to-one relationship to each other. Also, I can't see what you would achieve by it anyway???

At this point I don't know if it's a one-to-one relationship. If it's something like this, then I'd say it is not:

jedaisoul

Say the item is a car with a range of monochrome body colors (red, black or silver), that database structure would not be appropriate anyway.

Say the car has a two tone color scheme and a choice of tyres:

Color1 is the body color (red, black or silver).

Color2 is the color of the roof (body color or cream).

Color3 is the color of the tyres (black or white walled).

a) If there was only one combination of roof and tyre color per body color, then, again, that structure would be inappropriate.

b) Whereas if color1, color2 and color3 can be freely selected, then the structure might make sense.

However, even if b) applies, as there are (in this case) only 12 combinations, you could allocate a code to each combination and simply store the code rather than the individual colors.

NogDog

Without actually knowing why the OP's DB table has all those columns, it's pure speculation on my part. From past experience, my best guess would be that it probably indicates a flawed approach that would be better handled via one or more relational tables with foreign keys to the main table. But like I said earlier, at this point, with the limited information we have, it's only a "code smell" for now, with no way to say for sure one way or the other.