[Catalyst] Best Practices - an application.

[Mojo Nichols]

Next I create the database, essentially I have a systems table and users
as below.  This could probably be modeled better and comments are
welcome.   The systems contains the type of database to allow for
different databases management schemes. The database name to keep track
of multiple  databases on one server and the server name as in hostname.
User_roles for the system, so we can have custom routines for specific
roles (but I'm not sure this is the right place for this). This may be
better placed in the users table. 

The important fields in the users table are: user_role,
db_server,db_name, username.  The relation between users and systems is
many to many.  One system has many user, and one user can have access to
many systems.   

I have not had experience in doing slick many to many with dbix, so
input on this would be great.  I can add system to users relationship to
formalize the many to many, but have not yet. Do people have opinions on
mulitpart keys, to they work with DBIx.. etc.  Comments would be
welcome, and other ideas or models would be considered. 


Thanks

Mojo


-- MySQL dump 10.10
--
-- Host: localhost    Database: DMU
-- ------------------------------------------------------
-- Server version       5.0.21-standard-log

--
-- Table structure for table `systems`
--

DROP TABLE IF EXISTS `systems`;
CREATE TABLE `systems` (
  `db_type` varchar(8) NOT NULL,
  `db_name` varchar(15) NOT NULL,
  `user_role` varchar(16) NOT NULL,
  `db_server` varchar(16) NOT NULL,
  `alias` varchar(115) default NULL,
  KEY `db_server` (`db_server`,`db_name`,`db_type`,`user_role`)
);

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `grantee` varchar(15) NOT NULL,
  `user_role` varchar(20) NOT NULL,
  `db_name` varchar(20) NOT NULL,
  `db_server` varchar(15) default NULL,
  `last_name` varchar(20) default NULL,
  `first_name` varchar(15) default NULL,
  `email` varchar(40) default NULL,
  `location` varchar(3) default NULL,
  `username` varchar(10) default NULL,
  `change_date` timestamp,
  `update_flag` char(1) NOT NULL,
  KEY `db_server` (`db_server`,`db_name`,`user_role`,`username`)
);
[mnichols at localhost dmu]$