View source for AUS:v3

<font color=red size=16em>'''Deprecated by work on [[Balrog]].'''</font>

== 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.

== Proposed Solution ==

Using this less than completely normalized solution is theorized to be faster.  Rather than using joins, each table will have it's value looked up with an independent query.  Caching the results of these queries will minimize actual hits on the database.  

=== Collapsed ===

[[Image:AUSScehma.collapsed.complete.jpg]]

=== A Couple Problems ===

On beginning to translate the file system to the database I have growing concern that the database schema is not correct.  The diagram shows the updateParameters table having a foreign key to the updates table.  This couldn't be correct because the sample xml file shows more than one update being offered.  If the diagram were correct, only one update could be offered for a given set of input parameters.  

Reversing the relationship is not correct either.  If the foreign key were to be in the updates table pointing to the updateParameters table, then there could be more than one update offered for a given set of input parameters.  However, this would mean that an update is tied to a specific set of update parameters.  I wager that is not what is intended.

I believe that we need to add a join table to go between the the updateParameters table and the updates table.  This would enable a set of updates to server more than one set of input parameters.

However, I am concerned that an undesirable situation may develop. I generate rows in the updates and patches tables by reading the complete.txt and partial.txt files.  I create tentative row entries for the tables from the files and then look into the tables to see if rows identical to my tentative rows exist.  If an identical row exists, I throw away my tentative row and substitute the existing row.

Look at this scenario:

* .../somepath01/complete.txt translates to  updates row U1 and patches row P1.
* .../somepath01/partial.txt translates to updates row U1 and patches row P2.
* U1 is in the database only once, with P1 and P2 with references to it.
* U1 is associated with a row in updateParameters for 'somepath01'
* .../somepath02/complete.txt translates to updates row U1 and patches row P3.
* .../somepath02/partial.txt translates to updates row U1 and patches row P4.
* Since updates row U1 was already in the database, a new one was not created.  
* Patches rows P3 and P4 are given an association with U1.
* Since U1 is given an additional association with updateParameters row 'somepath02'
* All four patches rows P1, P2, P3 and P4 now have an association through updates row U1 with both 
updateParameters rows 'somepath01' and 'somepath02'.  

This was not expressed in the original file structure.  '''Is this situation likely to happen?  Is it undesirable?'''

==== Mapping Files To Tables ====
In mapping the pathname to database fields, each segment in the path leads to an entry in a database table:

incoming/2/'''Firefox'''/trunk/Linux_x86-gcc3/2007061204/en-US/complete.txt -> Product.name

incoming/2/Firefox/'''trunk'''/Linux_x86-gcc3/2007061204/en-US/complete.txt -> Version.name

incoming/2/Firefox/trunk/'''Linux_x86-gcc3'''/2007061204/en-US/complete.txt -> BuildTarget.name

incoming/2/Firefox/trunk/Linux_x86-gcc3/'''2007061204'''/en-US/complete.txt -> BuildId.name

incoming/2/Firefox/trunk/Linux_x86-gcc3/2007061204/'''en-US'''/complete.txt -> locale.name

incoming/'''2'''/Firefox/trunk/Linux_x86-gcc3/2007061204/en-US/complete.txt -> channel.name (2 - nightly; 3 - '''???''')

NULL  -> os_version.name

NULL  -> Distribution.name

NULL  -> Distibution_version.name

The rest of the values to be mapped into the database come from the '''complete.txt''' and '''partial.txt''' files.  An example mapping follows:

There are two file formats, one with keywords and one without
<pre>
complete  -> patches.type
http://ftp.mozilla.org/pub/mozilla.org/firefox/nightly/2007-06-12-07-trunk/firefox-3.0a6pre.en-US.linux-i686.complete.mar -> patches.URL
sha1 -> patches.hashFunction
4fe7d4db03ef96eb8f34bce05d740d986fcb0bc0 -> patches.hashValue
10752925 -> patches.size
2007061207 -> patches.build
3.0a6pre -> updates.version
3.0a6pre -> updates.extensionVersion
detailsUrl=http://www.mozilla.com/thunderbird/releases/1.5.0.14.html -> patches.detailsUrl (if present, otherwise NULL)
</pre>

updates.type is another complication.  It gets 'minor' if updates.type is 'nightly' otherwise it gets NULL

==== 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>generated</td> 
   </tr> 
   <tr>
      <td>updateType</td> 
      <td>integer</td>  
      <td>&nbsp;</td> 
      <td>1 - minor (default)<br/>2 - major (file: updateType line)</td> 
   </tr>
   <tr>
      <td>version</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: version line</td> 
   </tr> 
   <tr>
      <td>appv</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: appv line</td> 
   </tr> 
   <tr>
      <td>extv</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: extv line</td> 
   </tr> 
   <tr>
      <td>detailsUrl</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>NULL<br/>file: detailsURL line (if present)</td> 
   </tr> 
   <tr>
      <td>licenseUrl</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>NULL<br/>file: licenseUrl line (if present)</td> 
   </tr> 
</table>


<table border="1" cellpadding="5" cellspacing="0">
   <tr>
      <td style="background:#deadff;">id</td>
      <td style="background:#deadff;">updateType</td>      
      <td style="background:#deadff;">version</td>
      <td style="background:#deadff;"> extensionVersion </td>
      <td style="background:#deadff;"> detailsURL </td>
      <td style="background:#deadff;"> licenseUrl </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>
      <td>NULL</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>
      <td>NULL</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>generated</td> 
   </tr> 

   <tr>
      <td>type</td> 
      <td>integer</td>  
      <td>&nbsp;</td> 
      <td>1 - complete;<br/>2 - partial<br/>(file: type line)</td> 
   </tr>
   <tr>
      <td>url</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: url line</td> 
   </tr> 
   <tr>
      <td>hashFunction</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: hashFunction line</td> 
   </tr> 
   <tr>
      <td>hashValue</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: hashValue line</td> 
   </tr> 
   <tr>
      <td>size</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: size line</td> 
   </tr>
   <tr>
      <td>build</td> 
      <td>varchar</td>  
      <td>&nbsp;</td> 
      <td>file: build line</td> 
   </tr> 
   <tr>
      <td>update_id</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>

==== table 3: updateParameters ====
<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>product_id</td> 
      <td>integer</td>  
      <td>FK(product)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>version_id</td> 
      <td>integer</td>  
      <td>FK(version)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>buildId_id</td> 
      <td>integer</td>  
      <td>FK(build_id)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>buildTarget_id</td> 
      <td>integer</td>  
      <td>FK(build_target)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>locale_id</td> 
      <td>integer</td>  
      <td>FK(locale)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>channel_id</td> 
      <td>integer</td>  
      <td>FK(channel)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>osVersion_id</td> 
      <td>integer</td>  
      <td>FK(os_version)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>distribution_id</td> 
      <td>integer</td>  
      <td>FK(distribution)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>distributionVersion_id</td> 
      <td>integer</td>  
      <td>FK(distribution_version)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>update_id</td> 
      <td>integer</td>  
      <td>FK(updates)</td> 
      <td></td> 
   </tr> 
</table>

==== table 4: updateParametersToUpdates ====
<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>updateParameters_id</td> 
      <td>integer</td>  
      <td>FK(updateParameters)</td> 
      <td></td> 
   </tr> 
   <tr>
      <td>updates_id</td> 
      <td>integer</td>  
      <td>FK(updates)</td> 
      <td></td> 
   </tr> 

</table>

==== table 5: products ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 6: versions ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 7: buildIds ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 8: buildTargets ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 9: locales ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 10: channels ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 11: osVersions ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 12: distributions ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

==== table 13: distributionVersions ====
<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>name</td> 
      <td>varchar</td>  
      <td></td> 
      <td></td> 
   </tr> 
</table>

== Previous Proposals ==

=== 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: 10
* number of joins to get data: 9

'''Sample SQL:'''
<pre>
select 
  payload.* 
from 
  payload join product on (product.name = %PRODUCT% and product.id = payload.productId)  
            join version on (version.name = %VERSION% and version.id = payload.versionId)
              join build_id on (build_id.name = %BUILD_ID% and build_id.id = payload.build_idId)
                join build_target on (build_target.name = %BUILD_TARGET% and build_target.id = payload.build_targetId)
                  join locale on (locale.name = %LOCALE% and locale.id = payload.localId)
                    join channel on (channel.name = %CHANNEL% and channel.id = payload.channelId)
                      join os_version on (os_version.name = %OS_VERSION% and os_version.id = payload.os_versionId)
                        join distribution in (distribution.name = %DISTRIBUTION% and distribution.id = payload.distributionId)
                          join distibution_version on (distibution_version.name = %DISTRIBUTION_VERSION% and distibution_version.id = payload.distibution_versionId)
</pre>


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

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

=== Degenerate ===

''No Image''

The information is stored in a single table keyed by the entire URL.

'''Stats:'''
* number of tables: 1
* number of joins to get data: 0

'''Sample SQL:'''
<pre>
select payload.* from payload where url = %URL%
</pre>


'''Advantages'''
* fastest of the options
* no complicated SQL to maintain

'''Disadvantages'''
* no relationships between keys stored in the database

== 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.