On 6 Sep 2015, at 9:30am, sherry.ummen at gmail.com wrote: > Description(1..n) > Name > Desc > Records (0..n) > Number > Type > Values(1..n) > > Now its higly imposible to change this format because of legacy reason and > we use some proprietary single file database. > > Now we want to use new db for example sqlite but not sure how to model this > in sql mainly becaues "Records" have "Values" this is also an array of some > values depending on the Type (Int,String,Double). > > And "Description" can be sometimes of size 100Mb > > Could someone suggest what would be a good way to model this as SQL tables?
Three tables: Descriptions, Records, Values with relations between them. Check out FOREIGN KEYs: <https://www.sqlite.org/foreignkeys.html> I recommend that your key values (the ones which related one table to another) are rowids, not Name or record number. Like 'artistid' and 'trackid' in the examples on the above page. Don't worry about 100Mb in a field, or many of them in a table or database. SQLite has no problem with that. Simon.
