Is storing a delimited list in a database column really that bad? -

-

imagine web form set of check boxes (any or of them can selected). chose save them in comma separated list of values stored in 1 column of database table.

now, know correct solution create second table , normalize database. quicker implement easy solution, , wanted have proof-of-concept of application , without having spend time on it.

i thought saved time , simpler code worth in situation, defensible design choice, or should have normalized start?

some more context, small internal application replaces excel file stored on shared folder. i'm asking because i'm thinking cleaning program , make more maintainable. there things in there i'm not entirely happy with, 1 of them topic of question.

in addition violating first normal form because of repeating group of values stored in single column, comma-separated lists have lot of other more practical problems:

  • can’t ensure each value right data type: no way prevent 1,2,3,banana,5
  • can’t use foreign key constraints link values lookup table; no way enforce referential integrity.
  • can’t enforce uniqueness: no way prevent 1,2,3,3,3,5
  • can’t delete value list without fetching whole list.
  • can't store list longer fits in string column.
  • hard search entities given value in list; have use inefficient table-scan. may have resort regular expressions, example in mysql:
    idlist regexp '[[:<:]]2[[:>:]]'
  • hard count elements in list, or other aggregate queries.
  • hard join values lookup table reference.
  • hard fetch list in sorted order.
  • storing integers strings takes twice space storing binary integers. not mention space taken comma characters.

to solve these problems, have write tons of application code, reinventing functionality rdbms already provides more efficiently.

comma-separated lists wrong enough made first chapter in book: sql antipatterns: avoiding pitfalls of database programming.

there times when need employ denormalization, @omg ponies mentions, these exception cases. non-relational “optimization” benefits 1 type of query @ expense of other uses of data, sure know of queries need treated specially deserve denormalization.


Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more