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*
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
