On Oct 26, 2019, at 6:28 PM, J. King <jk...@jkingweb.ca> wrote: > > a good designer will choose a good schema from the start and thus rarely need > to change it
When you add new features to a long-lived application, you may occasionally have to ALTER the table(s) the app uses to allow the new feature. My company’s primary product has seen roughly two such DB alterations per year over its history, on average. (The alterations are probably slowing down in truth, as we asymptotically approach perfection. Hah!) SQLite has you covered when the alteration is just to add a new column or rename an existing one, but that’s not every case I’ve run into over my time writing DB-based software: 1. We added some DB columns in version N of our software to support new features required by clients, but then something like 5 years later, that technology dropped out of current use, so we dropped the feature and thus dropped those supporting DB columns. 2. We extended an existing feature of the software to allow new functionality, requiring DB table additions, then several major versions later we replaced that sidecar’d feature with a wholly new tech stack, including its own DB tables, so we dropped those intermediate-form DB columns after migrating the data. 3. An existing DB table had to have a column added to make the PRIMARY KEY unique again after we added a feature that would have allowed that column to hold duplicate data; the second column disambiguated the cases. SQLite lets you add the new column, but not change a table’s primary key without creating a new table and copying the data. SQLite avoids some of the need for this with its dynamic typing and its unwillingness to enforce length limits. We have several historical cases where we were using another DBMS and needed ALTER TABLE calls not allowed under SQLite to change column types or to widen fields. For example, we had a table that started out using an 8-bit data type to hold a TV channel number, since the highest channel number at the time was 125, so 8 bits was actually one more than we really required. Then digital TV happened. Then IPTV happened. Then OTT happened. Now we just have a string column and a bunch of logic to figure out whether the string is an analog TV channel number, a digital TV channel number, an IP address, a URL… If we’d been using SQLite from the start, we could have just kept using that “TINYINT” column to hold all of this, but ick. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
