dcsimg
www.webdeveloper.com
Results 1 to 8 of 8

Thread: multi column database table

  1. #1
    Join Date
    Sep 2016
    Posts
    97

    multi column database table

    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?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,152
    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.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Mar 2012
    Posts
    3,945
    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???

  4. #4
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    5,245
    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.
    --> JavaScript Frameworks like JQuery, Angular, Node <--
    ... and please remember to wrap code with forum BBCode tags:-

    [CODE]...[/CODE] [HTML]...[/HTML] [PHP]...[/PHP]

    If you can't think outside the box, you will be trapped forever with no escape...

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,152
    Quote Originally Posted by jedaisoul View Post
    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:
    Code:
    id
    description
    color_1
    size_1
    count_1
    color_2
    size_2
    count_2
    color_3
    size_3
    count3
    -- etc. --
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  6. #6
    Join Date
    Mar 2012
    Posts
    3,945
    I see your point, but should a database be structured that way? E.g.

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

    2. 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.
    Last edited by jedaisoul; 01-10-2017 at 04:06 PM.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,152
    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.
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  8. #8
    Join Date
    Mar 2012
    Posts
    3,945
    Agreed.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles