Database

[Jeff Mitchell]

If I understand the graph correctly, you have url and deviceid in uniqueid as 
foreign keys.  This is not the case.  Although I don't understand FKs too 
well as far as I understand it they're used to link data between two tables 
to ensure that values are consistent (or is it that when you look at a linked 
value you're actually looking at the same set of data as the referenced 
table?).  I'm not against making it a foreign key (or rather, I'd need to 
think about it a bit to make sure things would really be OK) of the url field 
in tags, though.  IIRC, the original reason that it wasn't a foreign key is 
that data in the uniqueid table was going to be persistent even if a 
particular track was removed from the collection.  As things progressed 
further I realized that this wasn't the correct way to do things as the 
values in that table could then get quickly out of hand.  So at this point it 
not being a foreign key is (again, IIRC) just...because  :-)

--Jeff

On Thursday 22 February 2007, Alexandre Oliveira wrote:
> I wanted to start some discussion about the database on IRC yesterday,
> but as I didn't understand the database very much myself, I wasn't
> very successful.
>
> I didn't follow very much the two deep changes on the database we had
> recently, and which happened almost together, the Dynamic Collections
> and AFT.
> Both changed the way our tables link, and they added some (necessary)
> complexity.
>
> Anyway, as I was really bored and I wanted to do something, I decided
> to build a diagram of the current shape of our database. After trying
> some apps like Kivio and Dia I run back to ErWin, which's a commercial
> app for windows that I had been obliged to use in the past, and thus I
> was quite familiar with.
>
> As we don't define foreign keys (only one for the labels!), the
> current picture of the database was actually bizarre, and wouldn't
> help at all with the planning. So I just got the tables and added what
> I think the proper links should be. Max and Jeff, please check if this
> is actually what you planned.
> Note that I'm not suggesting this schema, it's just supposed to be
> what we have now with 1.4.
>
> The idea is using this current schema to discuss what we can do as
> improvements, and a picture helps a lot IMO.
>
> For people not familiar with the notation, everything on the first
> part of each table, together, forms the primary key. Don't be scared
> about indices formed of 4 values. :-)
> The icons just represent the type of the data, FK means Foreign Key,
> and AK in this case represents the non primary Unique Keys (AK stands
> for Alternate Keys, but we don't have any non unique key).
> For the linkings, circle means 0, -- means one, and the symbol with
> the 3 dashes means many.
> Dashed lines mean non identifying relations, ie, a relation that adds
> a value that isn't used to identify the record.
>
>
> Sadly litesql doesn't seem to be ready for us, so I fear we'll end up
> either using qt stuff, that doesn't actually help much, or coding the
> support ourselves (in a better way, hopefully).