Re: Database schema [was: UU tools]

["Alexey Parshin" <alexeyp@gmail.com>]

It's a C++ approach, with no database support for integrity :(
Object id can be anything, and database can't prevent using course id for say, topic id. Foreign keys to the actual tables wouldn'd work in such situation. :(

The alternative approach is to have separate tables for different relations, like course_acl, topic_acl, etc.. It makes us to have multiple tables, but then we have foreign keys.
In order to satisfy fluffy C++ programmers, however, we may also have a view - a union of all the acl chunks looking as you defined..

2006/9/23, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com>:

something along the lines of
select user.* from course a, acl b, user.c where a.course_id=b.object_id
and b.user_id=c.user_id and b.access_type = ACCESS_TEACHER;

Alternatively, if access is bitfield, last part would be
b.access&ACCESS_TEACHER != 0


Alexey Parshin wrote:
> Ok, we came to the moment when we have to decide on ACL. Suppose, we
> have the ideal ACL in place. How would SELECT stmt look like, if you
> want to show all teachers for the particular course?
>
> 2006/8/9, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com
> <mailto: ilya@total-knowledge.com>>:
>
>     Alexey Parshin wrote:
>     > 2006/8/9, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com
>     <mailto: ilya@total-knowledge.com>>:
>     >> OK. Here are few comments, with the fact it's initial draft in
>     mind,
>     >> in no particular order.
>     >>
>     >> 1. Person -> course relationships.
>     >> Even currently we have more then just student and teacher in
>     our system.
>     >> There are "experts" there are "independent object authors"
>     (creating and
>     >> sharing objects other then courses), and in future there might be
>     >> even more.
>     >> So, I think instead of creating separate table for each of
>     these types,
>     >> we need
>     >> ACL table. Furthermore - this needs to be well thought through
>     - here is
>     >> an idea:
>     >> why don't we make globally unique object IDs, and have single
>     ACL table
>     >> for all
>     >> objects, not just courses. Again - I'm just throwing this
>     random idea
>     >> out here, and
>     >> am not sure what disadvantages are to it.
>     > I really don't like the ideas of ACL and global ids.
>     Why and Why?
>     > Probably, we can
>     > solve this the way unix does - we already have the object owner, we
>     > may also have a group of apprenices :)
>     Not really - we have more then that. Unix permissions are usually
>     rather
>     insufficient in situations with hierarchical access structures.
>     That is why
>     all modern Unices actually have ACLs ;-)
>     >> 2. Naming conventions. I suggest following changes to naming
>     >> conventions:
>     >>     a. All database names should be in singular form
>     >>     b. Do not add group qualifier to table names. i.e. "person"
>     instead
>     >> of "person_list"
>     >>         or "course_keyword" instead of "course_keywords".
>     >>     c. Respectively, we'll need to remove requirement for each
>     table to
>     >> be at least two
>     >>         words.
>     > I have developed several databases. The two of them that use the
>     > convention as it's described now are the most convenient for
>     everybody
>     > who uses them. For the small size database as we are building (I
>     > expect <50 tables) the two-letter prefix is enough to uniquly
>     identify
>     > the field in the database. For the TTS database, with ~120 tables, I
>     > even had to use a mix of two- and three-letter prefixes. Still,
>     when
>     > implementing something or discussing something, it allows to
>     name just
>     > the field name - and everybody understands what you're talking
>     about.
>     > So, I'd like to keep it the way it is.
>     I do not mind prefixes for fields - can you please address exact
>     changes
>     I proposed. Note: this is not to _replace_ your conventions, but
>     rather
>     to add
>     to them.
>     >> 3. problem_solutions table
>     >>     a. Why is it named that way? Sounds like "problem" is more
>     >> appropriate
>     >>     b. ps_xml - this is bit of misnomer, since not all problems
>     will
>     >> have XML in this field.
>     > It's a table of solutions for a problem. Come up with better
>     name. As
>     > for XML - I didn't come accross the description of how it should be
>     > stored. XML is the universal way, a container.
>     It stores more then solution, it stores problem itself as well. So
>     name
>     is just
>     "problem". As for XML, There are other, simpler formats, for
>     simpler tasks.
>     So, it is completely dependent on problem solver.
>     >> 4. What is "study_testing" table?
>     > There is a reference to the course testing, or something like this.
>     > I'm not sure - how it's related to anything yet.
>     >
>     >> 5. Documentation: can you add comments (fill out "Documentation"
>     >> property) for all
>     >>     tables, describing in short its purpose, and semantic
>     relationships
>     >> with others, where
>     >>     it may not be 100% obvious?
>     > I can and I will - as soon as we get the general skeleton more
>     or less
>     > formed.
>     >
>     >> 6. Keywords: what is intent behind all the _keywords tables?
>     > These are just proxy tables between actual keywords table and an
>     > object, just two foreign keys per row. I didn't want to store
>     separate
>     > lists of keywords per object type, however it's possible and I
>     don't
>     > insist much.
>     I still don't understand what it is for. Can you point me to
>     chapter in
>     specification, that prompted it?
>     >> Another thing: I'm going to set up SVN repository for UU some
>     time soon,
>     >> so we'll put
>     >> all these diagrams in there. Let's see how well can we cope with
>     >> conflicts in that ;-)
>     > Yeah, I thought about it. The XMI file is a text file (XML) - so it
>     > should stand corrections (merges) through CVS. However, I have
>     > discovered, that simply removing one important section may make the
>     > XMI file unreadable for umbrello. It reads such file, but shows no
>     > diagram - only object types and tables (on the left).
>     Yeap - that is what I'm worried about. It's XML, but it's machine
>     XML.
>
>     --
>     Ilya A. Volynets-Evenbakh
>     Total Knowledge. CTO
>     http://www.total-knowledge.com
>
>
>
>
> --
> Alexey Parshin,
> http://www.sptk.net

--
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com

-- 
Alexey Parshin,
http://www.sptk.net