View source for AUS:v3

== Database Schema Goals ==

# Referential integrity in data source
#* Add an RDBMS - MySQL likely - that uses a schema designed to store the data required for business rules and update metadata -- including data points we may not even track currently (these need to be defined).
# No duplication of data
#* Basic normalization (without OVER normalizing) should quite easily take care of this.

== Lookup System ==

=== Fully Normalized Hierarchy ===

[[Image:AUSSchema.complete.hierarchy.png]]

'''Stats:'''
* number of tables: 17
* number of joins to get data: 16

'''Sample SQL:'''
<pre>
select 
  payload.* 
from 
  product join pv on (product.name = %PRODUCT% and product.id = pv.productId)  
            join version on (version.name = %VERSION% and version.id = pv.versionId)
              join pvb on (pv.id = pvb.pvId)
                join build_id on (build_id.name = %BUILD_ID% and build_id.id = pvb.build_idId)
                  join pvbbt on (pvb.id = pvbbt.pvbId)
                    join build_target on (build_target.name = %BUILD_TARGET% and build_target.id = pvbbt.build_targetId)
                      join pvbbtl on (pvbbt.id = pcbbtl.pvbbtId)
                        join locale on (locale.name = %LOCALE% and locale.id = pvbbtl.localId)
                          join pvbbtlc on (pvbbtl.id = pvbbtlc.pvbbtlId)
                            join channel on (channel.name = %CHANNEL% and channel.id = pvbbtlc.channelId)
                              join pvbbtlcos on (pvbbtlc.id = pcvbbtlcos.pvbbtlcId)
                                join os_version on (os_version.name = %OS_VERSION% and os_version.id = pvbbtlcos.os_versionId)
                                  join pvbbtlcosd on (pvbbtlcos.id = pvbbtlcosd.pcbbtlcosId)
                                    join distribution in (distribution.name = %DISTRIBUTION% and distribution.id = pvbbtlcosd.distributionId)
                                      join payload on (pvbbtlcosd.id = payload.pvbbtlcosdId)
                                        join distibution_version on (distibution_version.name = %DISTRIBUTION_VERSION% and distibution_version.id = payload.distibution_versionId)
</pre>

'''Advantages:'''
* simulates the original hierarchical structure
* highly normalized

'''Disadvantages:'''
* very verbose SQL
* structure, while faithful to the previous system, doesn't necessarily accurately model the data relationships

=== Mostly Normalized Hierarchy with Rearrangement===

[[Image:AUSSChema.hierarchy.divided.png]]

'''Stats'''
* number of tables: 16
* number of joins: 15

'''Sample SQL'''
<pre>
select 
  payload.* 
from 
  payload join (product join pv on (product.name = %PRODUCT% and product.id = pv.productId)  
                          join version on (version.name = %VERSION% and version.id = pv.versionId)
                            join pvb on (pv.id = pvb.pvId)
                              join build_id on (build_id.name = %BUILD_ID% and build_id.id = pvb.build_idId))
                on payload.pvbId = pcbId.id
            join (btos join build_target on (build_target.name = %BUILD_TARGET% and build_target.id = btos.build_targetId)
                         join os_version on (os_version.name = %OS_VERSION% and os_version.id = btos.os_versionId)
                           join btosl on (btos.id = btosl.btosId)
                             join locale on (locale.name = %LOCALE% and locale.id = btosl.localeId))
                  on payload.btoslId = btosl.id
               join (cd join channel on (channel.name = %CHANNEL% and channel.id = cd.channelId)
                          join distribution on (distribution.name = %DISTRIBUTION% and distribution.id = cd.distributionId)
                            join cddv on (cd.id = cddv.cdId)
                              join distribution_version on (distribution_version.name = %DISTRIBUTION_VERSION% and distribution_version.id = cddv.distribution_versionId))
                     on payload.cddvId = cddv.id
</pre>

'''Advantages'''
* normalized
* rearrangement more accurately reflect the data relationships

'''Disadvantages'''
* complicated SQL



=== Collapsed ===

[[Image:AUSScehma.collapsed.png]]


'''Stats:'''
* number of tables: 17
* number of joins to get data: 16

'''Sample SQL:'''
<pre>
</pre>


'''Advantages'''
* fewer joins means faster queries

'''Disadvantages'''
* poor normalization
* no direct expression of the relationships between the keys


== Metadata First Cut ==

Converting an XML Schema into a relational database schema can be pretty straight forward.  An element generally corresponds with a row in a table.  The attributes inside the start tags correspond to the data in columns of the table.  Nested elements are rows in another table that has a foreign key relationship with the table to which the parent element is associated.  The tree structure of XML lends itself to orderly relational database schema.

While more complicated relationships can exist between the elements, the schema of this project seems very simple.

<pre>
<?xml version="1.0"?>

<updates>
  <update type="minor" version="1.0.4" extensionVersion="1.0" 
          detailsURL="http://www.foo.com/1.0.4/whatsnew.html">
    <patch type="partial" URL="http://www.foo.com/1.0.4-partial.mar"
           hashFunction="" hashValue="" size=""/>
    <patch type="complete" URL="http://www.foo.com/1.0.4-complete.mar"
           hashFunction="" hashValue="" size=""/>
  </update>
  ..
  <update type="major" version="1.1.2" extensionVersion="1.1"
          detailsURL="http://www.foo.com/1.1.2/whatsnew.html">
    <patch type="complete" URL="http://www.foo.com/1.1.2-complete.mar"
           hashFunction="" hashValue="" size=""/>
  </update>
</updates>
</pre>

=== AUS Metadata Database Schema Naive Version 1 === 

This proposal introduces just two tables: updates and patches.  They correspond to the major elements of the aforementioned XML file.

==== table 1: updates ====
<table border="1" cellpadding="5" cellspacing="0">
   <tr>
      <td style="background:#ffdead;">column name</td>
      <td style="background:#ffdead;">type</td>      
      <td style="background:#ffdead;">attribute</td>
      <td style="background:#ffdead;">comment</td>
   </tr> 
   <tr>
      <td>id</td> 
      <td>sequence</td>  
      <td>primary</td> 
      <td>&nbsp;</td> 
   </tr> 
   <tr>
      <td>type</td> 
      <td>varchar | enum | integer</td>  
      <td>&nbsp;</td> 
      <td>several options for implementation</td> 
   </tr>
   <tr>
      <td>version</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>likely a foreign key?</td> 
   </tr> 
   <tr>
      <td>extensionVersion</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>likely a foreign key?</td> 
   </tr> 
   <tr>
      <td>detailsURL</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>is this url completely unique?</td> 
   </tr> 
</table>


<table border="1" cellpadding="5" cellspacing="0">
   <tr>
      <td style="background:#deadff;">id</td>
      <td style="background:#deadff;">type</td>      
      <td style="background:#deadff;">version</td>
      <td style="background:#deadff;"> extensionVersion </td>
      <td style="background:#deadff;"> detailsURL </td>
   </tr> 
   <tr>
      <td>0</td>
      <td>minor</td>
      <td>1.0.4</td>
      <td>1.0</td>
      <td>http://www.foo.com/1.0.4/whatsnew.html</td>
   </tr>
   <tr>
      <td>1</td>
      <td>major</td>
      <td>1.1.2</td>
      <td>1.1</td>
      <td>http://www.foo.com/1.1.2/whatsnew.html</td>
   </tr>
</table>

==== table 2: patches ====
<table border="1" cellpadding="5" cellspacing="0">
   <tr>
      <td style="background:#ffdead;">column name</td>
      <td style="background:#ffdead;">type</td>      
      <td style="background:#ffdead;">attribute</td>
      <td style="background:#ffdead;">comment</td>
   </tr> 
   <tr>
      <td>id</td> 
      <td>sequence</td>  
      <td>primary</td> 
      <td>&nbsp;</td> 
   </tr> 

   <tr>
      <td>type</td> 
      <td>varchar | enum | integer</td>  
      <td>&nbsp;</td> 
      <td>several options for implementation</td> 
   </tr>
   <tr>
      <td>URL</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>is this url completely unique?</td> 
   </tr> 
   <tr>
      <td>hashFunction</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>&nbsp;</td> 
   </tr> 
   <tr>
      <td>hashValue</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>&nbsp;</td> 
   </tr> 
   <tr>
      <td>size</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>if it's never actually<br/>used as an integer</td> 
   </tr> 
   <tr>
      <td>updateId</td> 
      <td>integer</td>  
      <td>FK(updates)</td> 
      <td></td> 
   </tr> 
</table>


<table border="1" cellpadding="5" cellspacing="0">
   <tr>
      <td style="background:#deadff;">id</td>
      <td style="background:#deadff;">type</td>      
      <td style="background:#deadff;">URL</td>
      <td style="background:#deadff;">hashFunction</td>
      <td style="background:#deadff;">hashValue</td>
      <td style="background:#deadff;">size</td>
      <td style="background:#deadff;">updateId</td>
   </tr> 
   <tr>
      <td>0</td>
      <td>partial</td>
      <td>http://www.foo.com/1.0.4-partial.mar</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0</td>
   </tr> 
   <tr>
      <td>1</td>
      <td>complete</td>
      <td>http://www.foo.com/1.0.4-complete.mar</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>0</td>
   </tr> 
   <tr>
      <td>2</td>
      <td>complete</td>
      <td>http://www.foo.com/1.1.2-complete.mar</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>1</td>
   </tr>
</table>

=== Discussion ===

This schema is missing any indication of the product.  Does this system need to take into account more than just one product?  If is does, imagine another enclosing

'''How does this schema meet the goals at the top of the page?'''
# ''...data required for business rules and update metadata...'': What are the business rules?  Can't test to see if this is sufficient for the business rules until I have business rules.  If the "update metadata" is captured in its entirety by the XML example, the it is safe to judge that this database schema is equivalent.
# ''Basic normalization (without OVER normalizing)...'': This simple schema is normalized if the values in the columns are unique.  Each table has a type attribute that could be foreign keys to tables listing the allowed types.  Since there are so few options, splitting them off into their own tables would be over normalization.  The URL columns, if not unique within their tables, could be consolidated into their own tables leaving behind a foreign key.