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
