On 9 November 2016 at 12:36, Lahiru Cooray <[email protected]> wrote: > > > On Tue, Nov 8, 2016 at 1:23 PM, Uvindra Dias Jayasinha <[email protected]> > wrote: > >> >> >> On 8 November 2016 at 11:10, Lahiru Cooray <[email protected]> wrote: >> >>> >>> >>> On Thu, Nov 3, 2016 at 4:01 PM, Uvindra Dias Jayasinha <[email protected] >>> > wrote: >>> >>>> Hi All, >>>> >>>> Currently APIs have a few resources such as Swagger File, Optional WSDL >>>> file, Related Documents file and an Optional Thumbnail image that needs to >>>> be stored as blobs in the DB. >>>> >>>> Initially we thought of having separate tables to store these >>>> resources, but what if we have a single generic resource table to store all >>>> these? >>>> >>>> We could have schema such as below for the generic resource table >>>> >>>> >>>> Since we previously stored our resources in the registry, a similar >>>> generic schema was used to store all such resources by the registry itself. >>>> So anything that is not a text data type can be considered as a BLOB. >>>> >>>> The advantages of doing this are, >>>> >>>> 1. Can manage all API related resources from a central table without >>>> having to define custom tables for each resource. >>>> >>> +1 >>> >>>> 2. When an API is deleted its very easy to locate and remove all the >>>> resources related with it >>>> >>> +1 >>> >>>> 3. When a new version of an API is created its very easy to copy over >>>> the resources associated with the previous version to the new one. >>>> >>> Do we have a new API_ID for each version or do we have multiple version >>> no's against an API_ID? Then we need to maintain Version also as another >>> column >>> >> >> A new API version will have a new API_ID. >> >>> >>> >>>> WDYT? >>>> >>>> >>> +1 for the idea. >>> Also is DATA_TYPE mean the file extension? if so I suggest to rename the >>> name and also keep the file name as another column. >>> >> >> In some case we are saving URLs with DATA_TYPE column as TEXT so those >> aren't files. To be generic its better to keep this as DATA_TYPE >> > > In that case I have a different suggestion. Im not sure if maintaining > irrelevant data in same column is a good practice. > Can't we maintain the file type (eg: binary or inline) in the resources > types table which Akalanka suggested? > And in this transaction table, if the file type is inline we could store > the url in a separate column and if the file type is a binary we could > store binary value/file name/extension in separate columns. > > As mentioned earlier we dont store the data in the same column. There is a VARCHAR column(RESOURCE_TEXT_VALUE) and a BLOB column(RESOURCE_BINARY_VALUE). We are using a separate table to store the type as Akalanka suggested, below are the tables,
CREATE TABLE `AM_RESOURCE_TYPES` ( `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, `RESOURCE_TYPE_NAME` VARCHAR(255), PRIMARY KEY (`RESOURCE_TYPE_ID`) ); CREATE TABLE `AM_API_RESOURCES` ( `RESOURCE_ID` INTEGER AUTO_INCREMENT, `API_ID` INTEGER, `RESOURCE_TYPE_ID` INTEGER, `DATA_TYPE` VARCHAR(255), `RESOURCE_TEXT_VALUE` VARCHAR(1024), `RESOURCE_BINARY_VALUE` BLOB, PRIMARY KEY (`RESOURCE_ID`), FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`API_ID`) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) ON UPDATE CASCADE ON DELETE CASCADE ); > >>>> -- >>>> Regards, >>>> Uvindra >>>> >>>> Mobile: 777733962 >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> [email protected] >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> *Lahiru Cooray* >>> Software Engineer >>> WSO2, Inc.;http://wso2.com/ >>> lean.enterprise.middleware >>> >>> Mobile: +94 715 654154 >>> >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> Regards, >> Uvindra >> >> Mobile: 777733962 >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > *Lahiru Cooray* > Software Engineer > WSO2, Inc.;http://wso2.com/ > lean.enterprise.middleware > > Mobile: +94 715 654154 > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Regards, Uvindra Mobile: 777733962
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
