Hi, Well if I use an example to explain what I said, refer data_type column. Check the given sample values. xml,png,json are file extensions while text is a magic word/implementation logic we use to refer urls and inline text (file type). AFAIC these are different data. Even if we treat this as a generic field, we could further nomalize the DB structure having this field in resource type table.
eg: CREATE TABLE `AM_FILE_TYPES` ( `FILE_TYPE_ID` INTEGER AUTO_INCREMENT, `FILE_TYPE_NAME` VARCHAR(255), PRIMARY KEY (`FILE_TYPE_ID `) ); (file type: either blob/inline text or url) CREATE TABLE `AM_RESOURCE_TYPES` ( `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, `RESOURCE_TYPE_NAME` VARCHAR(255), `FILE_TYPE_ID ` INTEGER, PRIMARY KEY (`RESOURCE_TYPE_ID`), FOREIGN KEY (`FILE_TYPE_ID `) REFERENCES `AM_FILE_TYPES `(` FILE_TYPE_ID `) ON UPDATE CASCADE ON DELETE CASCADE, ); CREATE TABLE `AM_API_RESOURCES` ( `RESOURCE_ID` INTEGER AUTO_INCREMENT, `API_ID` INTEGER, `RESOURCE_TYPE_ID` INTEGER, // `DATA_TYPE` VARCHAR(255), [remove this field] `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 ); further if required, we could maintain a extension field in AM_API_RESOURCES table. I'm not telling that the proposed structure is wrong. This is just a suggestion to further normalize and minimize data repetition and to make the structure more clearer. On Wed, Nov 9, 2016 at 4:54 PM, Uvindra Dias Jayasinha <[email protected]> wrote: > > > 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 > > -- *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
