Further amended to include a resource name column, though this should be used for display purposes, download will happen via the RESOURCE_ID since that is the primary key
CREATE TABLE `AM_RESOURCE_DATA_TYPES` ( `DATA_TYPE_ID` VARCHAR(255), `DATA_TYPE_NAME` VARCHAR(255), PRIMARY KEY (`DATA_TYPE_ID`) ); CREATE TABLE `AM_RESOURCE_TYPES` ( `RESOURCE_TYPE_ID` VARCHAR(255), `DATA_TYPE_ID` INTEGER, `RESOURCE_TYPE_NAME` VARCHAR(255), PRIMARY KEY (`RESOURCE_TYPE_ID`), FOREIGN KEY (`DATA_TYPE_ID`) REFERENCES `AM_RESOURCE_DATA_TYPES`(`DATA_TYPE_ID`) ); CREATE TABLE `AM_API_RESOURCES` ( `RESOURCE_ID` VARCHAR(255), `API_ID` VARCHAR(255), *`RESOURCE_NAME` VARCHAR(255),* `RESOURCE_TYPE_ID` INTEGER, `RESOURCE_TEXT_VALUE` VARCHAR(1024), `RESOURCE_BINARY_VALUE` BLOB, PRIMARY KEY (`RESOURCE_ID`), FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`UUID`) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) ); On 18 November 2016 at 15:02, Tharindu Dharmarathna <[email protected]> wrote: > Hi Uvindra, > > Don't we need to keep the resource name inside the table . As we give > document download capability, We need to give the attachment name for > download. > > On Fri, Nov 18, 2016 at 12:22 PM, Uvindra Dias Jayasinha <[email protected] > > wrote: > >> Hi Lahiru, >> >> Yes what you have suggested is a lot more normalised, but I still prefer >> to refer to it as a DATA_TYPE as opposed to a FILE_TYPE. The reason is that >> File types are always stored in BLOB format where as shorter text data is >> stored as VARCHAR. So referring to free form text as a file type is >> misleading, because it isn't treated in the same way that files are >> treated. So to be more generic its better to consider these as Data Types. >> >> So this would be the more normalised table structure, >> >> CREATE TABLE `AM_RESOURCE_DATA_TYPES` ( >> `DATA_TYPE_ID` INTEGER AUTO_INCREMENT, >> `DATA_TYPE_NAME` VARCHAR(255), >> PRIMARY KEY (`DATA_TYPE_ID`) >> ); >> >> CREATE TABLE `AM_RESOURCE_TYPES` ( >> `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, >> `DATA_TYPE_ID` INTEGER, >> `RESOURCE_TYPE_NAME` VARCHAR(255), >> PRIMARY KEY (`RESOURCE_TYPE_ID`), >> FOREIGN KEY (`DATA_TYPE_ID`) REFERENCES `AM_RESOURCE_DATA_TYPES`(`DATA >> _TYPE_ID`) >> ); >> >> CREATE TABLE `AM_API_RESOURCES` ( >> `RESOURCE_ID` INTEGER AUTO_INCREMENT, >> `API_ID` VARCHAR(255), >> `RESOURCE_TYPE_ID` INTEGER, >> `RESOURCE_TEXT_VALUE` VARCHAR(1024), >> `RESOURCE_BINARY_VALUE` BLOB, >> PRIMARY KEY (`RESOURCE_ID`), >> FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`UUID`) ON UPDATE CASCADE ON >> DELETE CASCADE, >> FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES >> `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) >> ); >> >> >> >> On 13 November 2016 at 12:35, Lahiru Cooray <[email protected]> wrote: >> >>> 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 >>> >>> >> >> >> -- >> Regards, >> Uvindra >> >> Mobile: 777733962 >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > Thanks > > *Tharindu Dharmarathna*Software Engineer > WSO2 Inc.; http://wso2.com > lean.enterprise.middleware > > mobile: *+94779109091 <%2B94779109091>* > -- Regards, Uvindra Mobile: 777733962
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
