Database

Alexandre Oliveira aleprjlists at gmail.com
Sat Feb 24 20:11:29 CET 2007


I see, but I think everything that appears as keys in two different
tables should be foreign keys in one of them. It helps keeping the
database consistent, and actually makes things like deleting/editing
easier, if the correct constraints are set and the database enforces
them. I don't know if that's the case for sqlite, though.

deviceid isn't a problem at all, AFAIK they're never deleted. Right, Max?

I see your point about the urls, though. Maybe they should be actually
be on the uniqueid table, and be a FK on the tags table? It would mean
we'd always  need uniqueids for any url in the collection before we
add it to the tags table. Sounds sensible to me, can it be a problem
in any way?

Also, anyone knows about that playlists table?
Is the asin and locale enough to uniquely identify a amazon cover?

On 2/23/07, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
> 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).
>
>
> _______________________________________________
> Amarok-devel mailing list
> Amarok-devel at kde.org
> https://mail.kde.org/mailman/listinfo/amarok-devel
>


More information about the Amarok-devel mailing list