11:59 p.m.
I'm attaching my latest incarnation of the PackageDB schema. Here's the
run down on what's changed:
* Implementation of Collection inheritance/overlays/sets
* Add a reviewURL field to Package
* Restructure the Package hierarchy slightly:
Old:
Package Collection
| |
PackageListing
|
PackageVersion
New:
+-Collection Package
| | | |
| PackageListing PackageVersion
| |
+----------------->PackageVersionListing
The new structure allows a PackageVersion to exist in multiple
collections which Jesse says is beneficial when spinning short-term,
collections.
* PackageInterest revamped as a PackageACL set of tables. Instead of
having user roles (comaintainer, watcher, etc) we have functions that
the user can perform. The functions I've come up with so far are:
- commit: Commit changes to the VCS
- build: Request builds from the buildsystem
- watchbugzilla: Be informed of bugzilla tickets
- watchcommits: Be informed of VCS commits for this package
- approveacls: Change these ACLs for this package
- checkout: Checkout the package from the VCS. We'll normally set
this to true for a group that includes everyone but certain embargoed
branches may need to be set to a more restricted group.
Owners will implicitly have access to all of these. Sponsors or the
equivalent group of trusted contributors under the merged FC/FE will
belong to a group with commit-build-approveacls-checkout permissions.
Things that still need working on:
* Integrating comps/categories. I have some notes in comments. In
order to implement this we need to 1) decide if comps is the right thing
for this or if something closer to Nicolas Mailhot's suggestion is the
right way to go. 2) Give the PackageDB an understanding of binary
packages/subpackages as groups and categories won't be the same for each
subpackage. [Note unless someone wants to work on this, I'm deferring it
to after the first iteration.]
* Logging. Make sure we have all the tables we need for logging.
Verify that we really need to log on all these areas. Collections,
Packages, PackageListing(aka package in Collection), PackageVersion, and
PackageACL all have status fields so they should all have logs or we
need to evaluate whether we really want status for each of these.
* CollectionSets: Overlays can now be tracked in the database but the
logic for looking for packages has to be implemented. After talking
with Jesse, I think the simplest may be to implement this in application
code so that searching for a package will first check the collection,
then each of its bases. However, this imposes a performance penalty on
each select. It *may* be worthwhile to look into copying the packages
to the relevant overlay collections on insert instead. I have some
notes as comments in the schema.
* Set things up for SQLObject and SQLAlchemy. I'm going to try loading
the objects through database introspection and see how the two ORMs
fare.
-Toshio
2:16 a.m.
Le Mar 5 décembre 2006 06:59, Toshio Kuratomi a écrit :
* Integrating comps/categories. I have some notes in comments. In
order to implement this we need to 1) decide if comps is the right thing
for this or if something closer to Nicolas Mailhot's suggestion is the
right way to go.
Actually, I'd be fine if someone else's suggestion is taken as long as the
grouping and sorting problem is led to rest. With a working packagedb one
could hide the categorization in the db and only export final groups, but
that would create two classes of repositories : those with RW access to
the db and those without.
On a related issue I'd like to know if I should follow up on the comps
ralx-ng schema I posted on the other day, work on a schema for some new
comps format, or if the large round of silence that followed the last
schema posting means no one's really interested.
Regards,
--
Nicolas Mailhot
7:57 a.m.
On Mon, Dec 04, 2006 at 09:59:11PM -0800, Toshio Kuratomi wrote:
create table Collection (
id serial primary key,
name text not null,
version text not null,
status text not null default 'development',
owner integer not null,
publishURLTemplate text null,
From PostgreSQL docs:
NULL
The column is allowed to contain null values. This is the
default.
^^^^^^
This clause is only provided for compatibility with
non-standard SQL databases. Its use is discouraged in new
applications.
pendingURLTemplate text null,
summary text null,
description text null,
unique (name, version),
check (status = 'development' or status = 'active' or status =
'maintanence'
or status = 'EOL' or status = 'rejected')
);
From my point of view the status column is odd. I think you should
use an abbreviation or one char only.
status text not null default 'D'
check ( status IN
('D','A','M','E','R') )
create table Branch (
collectionId integer not null primary key,
branchName varchar(32) not null,
^^^^^^^^^
distTag varchar(32) not null,
^^^^^^
is it right define duplicate tags and branch names?
parentId integer null,
foreign key (parentId) references Collection(id),
foreign key (collectionId) references Collection(id)
);
Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
you should merge the tables to one table only.) Maybe:
create table Branch (
id serial primary key,
branchName varchar(32) not null unique,
distTag varchar(32) not null unique,
parentId integer references Collection(id),
collectionId integer not null references Collection(id)
);
Also, I think there should be defined some FK policy for update and
delete. It means "ON DELETE" and "ON UPDATE" definition for the
references.
create table Package (
id serial primary key,
name text not null unique,
summary text not null,
description text null,
reviewURL text null,
status text not null default 'awaitingreview',
check (status = 'awaitingreview' or status = 'underreview' or status =
'approved' or status = 'denied')
);
IMHO, same problem with the status column. Here it is more terrible,
because this table will be larger.
CHECK( status IN (
'W', -- wait
'R', -- review
'A', -- approved
'D' -- denied
))
-- Permissions for who can make various changes to the code.
-- We want to limit the access that a given person may have to edit the package
--
-- Fields:
-- :id: Primary key
-- :pkgListId: What package in what collection has this value.
-- :acl: The permission being set.
-- :status: Whether this permission is active.
create table PackageACL (
id serial primary key,
packageListingId integer not null,
acl text not null,
status text not null,
foreign key (packageListingId) references PackageListing(id),
check (status = 'awaitingreview' or status = 'approved' or status =
'denied'
or status = 'obsolete'),
check (acl = 'commit' or acl = 'build' or acl =
'watchbugzilla'
or acl = 'watchcommits' or acl = 'approveacls' or acl =
'checkout')
);
-- ACLs that allow a person to do something
--
-- Fields:
-- :packageACLId: Inherit from an ACL record.
-- :userId: User id from the account system.
create table PersonPackageACL (
packageACLId integer primary key,
userId integer not null,
foreign key (packageACLId) references PackageACL (id)
);
Again. 1:1 model (PK=FK).
I think the ACL model should be:
what (ACL), where (package), who (user)
you can use one table only (or two if ACL is group of permissions).
-- ACLs that allow a group to do something
--
-- Fields:
-- :packageACLId: Inherit from an ACL record.
-- :groupId: Group id from the account system.
create table GroupPackagePermissions (
packageACLId integer primary key,
groupId integer not null,
foreign key (PackageACLId) references PackageACL (id)
);
Again. 1:1
-- Log a change to the packageDB.
--
-- Fields:
-- :id: Primary key
-- :userId: Who made the change.
-- :changeTime: Time that the change occurred.
create table Log (
id serial primary key,
userId integer not null,
changeTime timestamp default now() not null
);
When, Who, ... and where is "What"? (type of change)
-- Log a change made to the Package table.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageId: The package that changed.
-- :action: What happened to the package.
-- :description: Additional information about the change.
create table PackageLog (
logId integer primary key,
packageId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'removed' or action =
'statuschanged' or
action = 'awaitingreview' or action = 'underreview' or action =
'approved'
or action = 'denied'),
foreign key (logId) references Log(id),
foreign key (packageId) references Package(id)
);
... ah... here is the "What". Again 1:1 (PK=FK).
Don't forget PRIMARY KEY is always UNIQUE.
-- Log changes to packages in collections.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageListingId: The packageListing that changed.
-- :action: What happened to the package in the collection.
-- :description: Additional information about the change.
create table PackageListingLog (
logId integer primary key,
packageListingId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'removed' or action =
'awaitingreview'
or action = 'awaitingbranch' or action = 'underreview' or
action = 'approved' or action = 'denied'),
foreign key (logId) references Log (id),
foreign key (packageListingId) references PackageListing(id)
);
Again. 1:1.
-- Log changes to built packages.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageVersionId: The `PackageVersion` that changed.
-- :action: What happened to the `PackageVersion`.
-- :description: Additional information about the change.
create table PackageVersionLog (
logId integer primary key,
packageVersionId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'awaitingdevel' or
action = 'awaitingreview' or action = 'awaitingqa' or
action = 'aaitingpublish' or action = 'approved' or action =
'denied' or
action = 'obsolete'),
foreign key (logId) references Log (id),
foreign key (packageVersionId) references PackageVersion(id)
);
Again. 1:1.
-- Log changes to built package ACLs.
--
-- Fields:
-- :logId: The id of the log entry.
-- :packageVersionId: The `PackageACL` that changed.
-- :action: What happened to the ACLs for the package.
-- :description: Additional information about the change.
create table PackageACLLog (
logId integer primary key,
packageACLId integer not null,
action text not null,
description text null,
check (action = 'added' or action = 'awaitingreview'
or action = 'awaitingbranch' or action = 'underreview' or
action = 'approved' or action = 'denied' or action =
'obsolete'),
foreign key (logId) references Log (id),
foreign key (packageACLId) references PackageACL(id)
);
Again. 1:1.
Karel
--
Karel Zak <kzak(a)redhat.com>
1:03 p.m.
O/H Karel Zak έγραψε:
On Mon, Dec 04, 2006 at 09:59:11PM -0800, Toshio Kuratomi wrote:
> pendingURLTemplate text null,
> summary text null,
> description text null,
> unique (name, version),
> check (status = 'development' or status = 'active' or status =
'maintanence'
> or status = 'EOL' or status = 'rejected')
> );
From my point of view the status column is odd. I think you should
use an abbreviation or one char only.
status text not null default 'D'
check ( status IN
('D','A','M','E','R') )
It would be best to keep open the possibility that these (and other similar)
flags might change in the future, happen to have conflicting first letters etc.
-d
--
Dimitris Glezos
Jabber ID: glezos(a)jabber.org, GPG: 0xA5A04C3B
http://dimitris.glezos.com/
"He who gives up functionality for ease of use
loses both and deserves neither." (Anonymous)
--
1:32 p.m.
Thanks for the feedback!
On Tue, 2006-12-05 at 14:57 +0100, Karel Zak wrote:
On Mon, Dec 04, 2006 at 09:59:11PM -0800, Toshio Kuratomi wrote:
> create table Collection (
> id serial primary key,
> name text not null,
> version text not null,
> status text not null default 'development',
> owner integer not null,
> publishURLTemplate text null,
From PostgreSQL docs:
NULL
The column is allowed to contain null values. This is the
default.
^^^^^^
This clause is only provided for compatibility with
non-standard SQL databases. Its use is discouraged in new
applications.
Okay. I'll make this implicit.
> pendingURLTemplate text null,
> summary text null,
> description text null,
> unique (name, version),
> check (status = 'development' or status = 'active' or status =
'maintanence'
> or status = 'EOL' or status = 'rejected')
> );
From my point of view the status column is odd. I think you should
use an abbreviation or one char only.
status text not null default 'D'
check ( status IN
('D','A','M','E','R') )
What's the justification? Here's my reasoning:
[Single letter code]
+ Harder to misspell
+ Takes less storage space
[Full words]
+ Less cryptic
We could solve both concerns by having a foreign key constraint into a
table with the valid status phrases for each table that needs statuses
but that makes things more complex. This would allow us to select the
list of valid statuses from a database table which is a plus. But it
would also require a join for the common case of giving a human readable
name for the status. So I'd like to hear your justification.
>
> create table Branch (
> collectionId integer not null primary key,
> branchName varchar(32) not null,
^^^^^^^^^
> distTag varchar(32) not null,
^^^^^^
is it right define duplicate tags and branch names?
distTag and branchName are different pieces of data.
From the comments:
-- :branchName: Name of the branch in the VCS ("FC-3", "devel")
-- :distTag: DistTag used in the buildsystem (".fc3", ".fc6")
It may be unfortunate that we don't use the same names for both, but
it's the way things are.
> parentId integer null,
> foreign key (parentId) references Collection(id),
> foreign key (collectionId) references Collection(id)
> );
Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
you should merge the tables to one table only.) Maybe:
create table Branch (
id serial primary key,
branchName varchar(32) not null unique,
distTag varchar(32) not null unique,
parentId integer references Collection(id),
collectionId integer not null references Collection(id)
);
We're actually modeling a 1:[0 1] relationship. For programmers, this
would be inheritance.
Also, I think there should be defined some FK policy for update and
delete. It means "ON DELETE" and "ON UPDATE" definition for the
references.
You're correct. It's something I forgot to add to my list of todo items
at the bottom.
[snip lots of comments on 1:1 and succinct status fields]
If you can show that inheritance is bad or that there's a compelling
reason to make status fields a single character, I can change all the
instances.
-Toshio
4:10 p.m.
On Tue, Dec 05, 2006 at 11:32:29AM -0800, Toshio Kuratomi wrote:
> status text not null default 'D'
> check ( status IN
('D','A','M','E','R') )
>
What's the justification? Here's my reasoning:
[Single letter code]
+ Harder to misspell
+ Takes less storage space
+ Takes less space for data transfer between client and DB engine
[Full words]
+ Less cryptic
cryptic? Who will be typical client for your DB? Human or script? ;-)
We could solve both concerns by having a foreign key constraint into
a
table with the valid status phrases for each table that needs statuses
Yes. It's a good way *in case* you need modify/add status types,
otherwise it's over engineering. (FK is nothing cheap for DB engine.)
but that makes things more complex. This would allow us to select
the
list of valid statuses from a database table which is a plus. But it
would also require a join for the common case of giving a human readable
name for the status. So I'd like to hear your justification.
> >
> > create table Branch (
> > collectionId integer not null primary key,
> > branchName varchar(32) not null,
> ^^^^^^^^^
> > distTag varchar(32) not null,
> ^^^^^^
> is it right define duplicate tags and branch names?
>
distTag and branchName are different pieces of data.
From the comments:
-- :branchName: Name of the branch in the VCS ("FC-3", "devel")
-- :distTag: DistTag used in the buildsystem (".fc3", ".fc6")
It may be unfortunate that we don't use the same names for both, but
it's the way things are.
Well, the question is: is it expected that there will be same DistTag
for different branches? If not.. define distTag as UNIQUE.
> > parentId integer null,
> > foreign key (parentId) references Collection(id),
> > foreign key (collectionId) references Collection(id)
> > );
>
> Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
> you should merge the tables to one table only.) Maybe:
>
> create table Branch (
> id serial primary key,
> branchName varchar(32) not null unique,
> distTag varchar(32) not null unique,
> parentId integer references Collection(id),
> collectionId integer not null references Collection(id)
> );
>
We're actually modeling a 1:[0 1] relationship. For programmers, this
would be inheritance.
Hint: never think about programmers and DB usage when you work on DB
design. Think about data and relatioships between data only :-)
I don't have time to study data and real relatioships for this DB, but
1:1 is usually very strange. (and 1:0 = PK:NULL in normal table).
Karel
--
Karel Zak <kzak(a)redhat.com>
5:04 p.m.
On Tue, 2006-12-05 at 23:10 +0100, Karel Zak wrote:
On Tue, Dec 05, 2006 at 11:32:29AM -0800, Toshio Kuratomi wrote:
> > status text not null default 'D'
> > check ( status IN
('D','A','M','E','R') )
> >
> What's the justification? Here's my reasoning:
> [Single letter code]
> + Harder to misspell
> + Takes less storage space
+ Takes less space for data transfer between client and DB engine
> [Full words]
> + Less cryptic
cryptic? Who will be typical client for your DB? Human or script? ;-)
Humans :-)
If we only keep codes in the database, then application level code has
to translate that into something human readable. Then you end up with
duplicate code when more than one application is utilizing the database.
It's much better to keep the information somewhere in the database.
> We could solve both concerns by having a foreign key constraint
into a
> table with the valid status phrases for each table that needs statuses
Yes. It's a good way *in case* you need modify/add status types,
otherwise it's over engineering. (FK is nothing cheap for DB engine.)
Right. I'm not projecting needing to modify status types too
frequently, so I figured that FK would be overkill.
Jeffrey Ollie brought up some l10n and i18n ideas wrt this, though.
I'll pull that discussion back into this thread.
> but that makes things more complex. This would allow us to
select the
> list of valid statuses from a database table which is a plus. But it
> would also require a join for the common case of giving a human readable
> name for the status. So I'd like to hear your justification.
>
> > >
> > > create table Branch (
> > > collectionId integer not null primary key,
> > > branchName varchar(32) not null,
> > ^^^^^^^^^
> > > distTag varchar(32) not null,
> > ^^^^^^
> > is it right define duplicate tags and branch names?
> >
> distTag and branchName are different pieces of data.
> From the comments:
> -- :branchName: Name of the branch in the VCS ("FC-3", "devel")
> -- :distTag: DistTag used in the buildsystem (".fc3", ".fc6")
>
> It may be unfortunate that we don't use the same names for both, but
> it's the way things are.
Well, the question is: is it expected that there will be same DistTag
for different branches? If not.. define distTag as UNIQUE.
Ah. I understand you now. Yes, both distTag and branchName can be
unique. I'll change that.
> > > parentId integer null,
> > > foreign key (parentId) references Collection(id),
> > > foreign key (collectionId) references Collection(id)
> > > );
> >
> > Hmm.. here I see 1:1 model (PK=FK). Strange. (It usually means that
> > you should merge the tables to one table only.) Maybe:
> >
> > create table Branch (
> > id serial primary key,
> > branchName varchar(32) not null unique,
> > distTag varchar(32) not null unique,
> > parentId integer references Collection(id),
> > collectionId integer not null references Collection(id)
> > );
> >
> We're actually modeling a 1:[0 1] relationship. For programmers, this
> would be inheritance.
Hint: never think about programmers and DB usage when you work on DB
design. Think about data and relatioships between data only :-)
Generally good. But in this case we're working with an Object
Relational Mapper so we have to pay some attention to the programmer as
well. Additionally, we *are* dealing with relationships between data
here, it's just that it's 1:[0 1] which is covered much less frequently
than 1:1 (mostly avoid), 1:many, and many:many (transform into 1:many)
I don't have time to study data and real relatioships for this
DB, but
1:1 is usually very strange. (and 1:0 = PK:NULL in normal table).
Right, 1:1 is usually not necessary. 1:0 makes no sense :-) This is
neither of those. 1:[0 1] does not make PK = NULL. It means that there
will be some records in Collection for which there is also a record in
Branch and some for which no branch record exists at all.
PostgreSQL and SQL-1999 both have ways of modeling this. But
PostgreSQL's has some caveats that we might run into and neither
PostgreSQL nor MySQL support the SQL-1999 construct.
Here's some documentation on PostgreSQL's inheritance feature:
http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html
and some documentation on the interface with the ORM:
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_inherit...
-Toshio
8:19 a.m.
On Dec 5, 2006, at 2:32 PM, Toshio Kuratomi wrote:
> status text not null default 'D'
> check ( status IN
('D','A','M','E','R') )
>
What's the justification? Here's my reasoning:
[Single letter code]
+ Harder to misspell
+ Takes less storage space
[Full words]
You're sticking the status into a column of type 'TEXT', though. TEXT
is not even like varchar which defaults to 1024 characters or so of
storage. I believe TEXT tells the database 'be ready to store between
zero and 4 billion bytes in this column'.
If you want to cut down on storage space, check the field types and
specify maximum sizes. In this case, varchar(1) would be a more
appropriate type. Since the database will wind up storing this field
using at least 4 bytes of space for alignment purposes, might as well
take it to varchar(3) or so to also allow for future expansion (and
maybe a bit more readability).
To balance fast indexing/comparison, small storage space, and room
for expansion, it's often easier to use INTEGER for these type of
columns, and then assign meaning to those values elsewhere (either
through a separate table that translates values to strings, or by
#define-like constants in the source code).
</stuff you never wanted to know about choosing types for DB columns> :)
Best,
-- Elliot
1:19 p.m.
On Wed, 2006-12-06 at 09:19 -0500, Elliot Lee wrote:
On Dec 5, 2006, at 2:32 PM, Toshio Kuratomi wrote:
>> status text not null default 'D'
>> check ( status IN
('D','A','M','E','R') )
>>
> What's the justification? Here's my reasoning:
> [Single letter code]
> + Harder to misspell
> + Takes less storage space
> [Full words]
You're sticking the status into a column of type 'TEXT', though. TEXT
is not even like varchar which defaults to 1024 characters or so of
storage. I believe TEXT tells the database 'be ready to store between
zero and 4 billion bytes in this column'.
varchar and text have had equivalent performance and storage in
postgreSQL for a long time. According to the 8.2 docs[1]_, char is
currently no better (and it space pads your entries so there's probably
no reason to use it if you aren't planning on switching databases) (This
change appears to have been part of 7.1 [2]_)
This assumes you store the same values in each column. The value of
varchar is it allows you to specify a limit which can prevent the user
from using 'supercalifragilisticexpialadocious' as a value. But in most
of our use cases, allowing room for expansion seems fine. In this
particular case, we're using a constraint to prevent random values so we
wouldn't be providing any additional limitations on the user's ability
to enter long data.
[1]_
http://www.postgresql.org/docs/8.2/interactive/datatype-character.html
[2]_ http://www.postgresql.org/docs/7.2/static/release-7-1.html
If you want to cut down on storage space, check the field types and
specify maximum sizes. In this case, varchar(1) would be a more
appropriate type. Since the database will wind up storing this field
using at least 4 bytes of space for alignment purposes, might as well
take it to varchar(3) or so to also allow for future expansion (and
maybe a bit more readability).
I'm not too concerned with storage space bloat from the status field.
Karel's clarification about db-to-client transfer makes sense. However,
I'm in the camp that believes the status strings belong in the database
so I think the typical usage will transfer the same amount of data. (If
the statusTranslations table is cached this might be better, though...
more room for optimization if we have separate tables.)
To balance fast indexing/comparison, small storage space, and room
for expansion, it's often easier to use INTEGER for these type of
columns, and then assign meaning to those values elsewhere (either
through a separate table that translates values to strings, or by
#define-like constants in the source code).
I think we'll do this with INTEGER and a separate table because Jeffrey
Ollie's proposal to allow translations to the status codes makes sense.
I really hate having constants in source code because it hinders the
database's ability to enforce integrity. The source code of the
application has to stay in sync with the database. If there's more than
one consumer, each one has to stay in sync. Enforcing this is a problem
that the database was designed to solve.
</stuff you never wanted to know about choosing types for DB
columns> :)
I thought about this as I modified the schema, really! ;-)
-Toshio
5 p.m.
On Wed, Dec 06, 2006 at 11:19:29AM -0800, Toshio Kuratomi wrote:
On Wed, 2006-12-06 at 09:19 -0500, Elliot Lee wrote:
> On Dec 5, 2006, at 2:32 PM, Toshio Kuratomi wrote:
> >> status text not null default 'D'
> >> check ( status IN
('D','A','M','E','R') )
> >>
> > What's the justification? Here's my reasoning:
> > [Single letter code]
> > + Harder to misspell
> > + Takes less storage space
> > [Full words]
>
> You're sticking the status into a column of type 'TEXT', though. TEXT
> is not even like varchar which defaults to 1024 characters or so of
> storage. I believe TEXT tells the database 'be ready to store between
> zero and 4 billion bytes in this column'.
>
varchar and text have had equivalent performance and storage in
Definitely true. All internal PostgreSQL routines uses same type
(text) for almost all operations. The varchar(n) makes sense if you
want to restrict something in your data model, but it's not important
for performance and storage.
> To balance fast indexing/comparison, small storage space, and
room
> for expansion, it's often easier to use INTEGER for these type of
> columns, and then assign meaning to those values elsewhere (either
> through a separate table that translates values to strings, or by
> #define-like constants in the source code).
>
I think we'll do this with INTEGER and a separate table because Jeffrey
Ollie's proposal to allow translations to the status codes makes sense.
It depends on number of status codes. You can use 1 or 2 chars as a
primary key for status table (instead integers). It's better for
humans, because simple selects (without join to status table) are
still readable.
Karel
--
Karel Zak <kzak(a)redhat.com>
6:21 p.m.
On Thu, 2006-12-07 at 00:00 +0100, Karel Zak wrote:
On Wed, Dec 06, 2006 at 11:19:29AM -0800, Toshio Kuratomi wrote:
> On Wed, 2006-12-06 at 09:19 -0500, Elliot Lee wrote:
> > To balance fast indexing/comparison, small storage space, and room
> > for expansion, it's often easier to use INTEGER for these type of
> > columns, and then assign meaning to those values elsewhere (either
> > through a separate table that translates values to strings, or by
> > #define-like constants in the source code).
> >
> I think we'll do this with INTEGER and a separate table because Jeffrey
> Ollie's proposal to allow translations to the status codes makes sense.
It depends on number of status codes. You can use 1 or 2 chars as a
primary key for status table (instead integers). It's better for
humans, because simple selects (without join to status table) are
still readable.
Somewhat. We have 15 status codes right now. Even with three letter
abbreviations, you have to have a pretty good idea what the possible
statuses are in order to make use of it in "raw" form. Here are a few
of the problems:
+ 7 of the statuses begin with "a"
+ Many of our statuses are two words
+ We have status's like "Under Review" and "Awaiting Review"
which
prevents simplifying to a mnemonic for the most significant word.
Not sure that it's worthwhile to try to make these status codes directly
human readable or not.
-Toshio
6350
days inactive
6350
days old
infrastructure@lists.fedoraproject.org
10 comments
5 participants
participants (5)
-
Dimitris Glezos -
Elliot Lee -
Karel Zak -
Nicolas Mailhot -
Toshio Kuratomi