Data Dictionary And Database Report

Created by SlaphappyGeeks.com

Project name Target db Author Copyright Current
example mysql4 Robert Allen 2007 Robert Allen 2007-12-05 01:47
Description: An example database for keeping user contact and support history information, and setting access permissions.

Generated by Allen Technology DDictXSLT - Copyright 2004 allentech.net

<< Return to DB index

Data Dictionary Index

Entity - Attribute - Index Details
Domains
Relationships
ER Diagram

ER Diagram

example

Entities

email_addr
permission
phone
user
user_history

^Top, Data Dictionary Index

Entity: email_addr Email address (no todo)
Attribute Data type / Check Default value / Req'd Description TODO
confirmed
Domain: boolean
BOOL
(no check defined)
(no default)
NOT NULL
Set if email address has been confirmed (no todo)
email_addr
Domain: email
VARCHAR, 64
(no check defined)
(no default)
NOT NULL
Valid email address (no todo)
PKemail_addr_id
INTEGER, AUTONUMBER
(no check defined)
(no default)
NOT NULL
Record unique id (no todo)
type
Domain: text_16
VARCHAR, 16
(no check defined)
(no default)
NOT NULL
Primary use of this address: Ex - Personal, Business, Emergency, All (no todo)
FKuser_id
INTEGER
(no check defined)
(no default)
NOT NULL
Foreign key into user table (no todo)

^Top, Data Dictionary Index, Entities Index

Entity: permission User access permission levels (no todo)
Attribute Data type / Check Default value / Req'd Description TODO
level
Domain: text_16
VARCHAR, 16
(no check defined)
(no default)
NOT NULL
Name of assigned permission level: Ex - Anonymous, Member, Private member, Admin, Super admin (no todo)
PKpermission
TINYINT
(no check defined)
0
NOT NULL
Primary key, also permission level value, > permission is > access (no todo)

^Top, Data Dictionary Index, Entities Index

Entity: phone Phone numbers Research int'l phone number formats to be certain all are covered by component string lengths
Attribute Data type / Check Default value / Req'd Description TODO
area_code
VARCHAR, 4
(no check defined)
(no default)
NOT NULL
String representation of area code (no todo)
country_code
VARCHAR, 4
(no check defined)
(no default)
NOT NULL
String representation of country code (no todo)
ext
VARCHAR, 8
(no check defined)
none
NOT NULL
Phone number extension (no todo)
phone
VARCHAR, 12
(no check defined)
(no default)
NOT NULL
String representation of phone number Research int'l phone number formats to be certain all are covered by component string lengths
PKphone_id
INTEGER, AUTONUMBER
(no check defined)
(no default)
NOT NULL
Record unique id (no todo)
type
Domain: text_16
VARCHAR, 16
(no check defined)
(no default)
NOT NULL
Type of number: Ex - Home, Business, Cell, Personal, Fax, etc. (no todo)
FKuser_id
INTEGER
(no check defined)
(no default)
NOT NULL
Foreign key into user table (no todo)
Index Type / Sort Attributes Description TODO
AC_IDX
Default
Default
area_code
 (no description) (no todo)
CC_IDX
Default
Default
country_code
 (no description) (no todo)

^Top, Data Dictionary Index, Entities Index

Entity: user (no description) (no todo)
Attribute Data type / Check Default value / Req'd Description TODO
birthdate
DATE
(no check defined)
(no default)
NOT NULL
User's date of birth (no todo)
enabled
Domain: boolean
BOOL
(no check defined)
(no default)
NOT NULL
Enables user access if set (no todo)
first_name
Domain: text_32
VARCHAR, 32
(no check defined)
unknown
NOT NULL
First name of user (no todo)
language
Domain: text_32
VARCHAR, 32
(no check defined)
unknown
NOT NULL
User's primary language (no todo)
last_name
Domain: text_32
VARCHAR, 32
(no check defined)
unknown
NOT NULL
Last name of user (no todo)
pass
Domain: passwd
VARCHAR, 12
(no check defined)
(no default)
NOT NULL
User password (encrypted) (no todo)
FKpermission
TINYINT
(no check defined)
(no default)
NOT NULL
Foreign key into permission table, also integer access permission level (no todo)
sex
ENUM, ('M','F','unknown')
(no check defined)
Unk
NOT NULL
Users gender (no todo)
PKuser_id
INTEGER, AUTONUMBER
(no check defined)
(no default)
NOT NULL
Record unique id (no todo)
user_name
Domain: text_16
VARCHAR, 16
(no check defined)
(no default)
NOT NULL
User login name (no todo)

^Top, Data Dictionary Index, Entities Index

Entity: user_history User admin history (no todo)
Attribute Data type / Check Default value / Req'd Description TODO
admin_notes
Domain: description
TEXT
(no check defined)
(no default)
NULL
Notes entered by admin handling user event (no todo)
event
Domain: text_32
VARCHAR, 32
(no check defined)
(no default)
NOT NULL
Type of user event: Ex - signup, enabled, disabled (no todo)
PKhist_id
INTEGER, AUTONUMBER
(no check defined)
(no default)
NOT NULL
Record unique id (no todo)
timestamp
Domain: unix_time
INTEGER
(no check defined)
(no default)
NOT NULL
Record timestamp (no todo)
FKuser_id
INTEGER
(no check defined)
(no default)
NOT NULL
Foreign key into user table (no todo)

^Top, Data Dictionary Index, Entities Index

Domains

boolean
date_time
description
email
ip_address
number_dec42
number_double
number_int
passwd
short_description
text_128
text_16
text_255
text_32
text_64
unix_time
url
us_dollar
us_phone
us_state
us_zip
username

^Top, Data Dictionary Index

Domain name Data type / Check Default value Description TODO
boolean
usage: 2
BOOL
value in (1,0)
0 Boolean type supported by view_base_class_2 (no todo)
date_time
usage: 0
DATETIME
valid date and time value
(no default) DATETIME type supported by view_base_class_2 (no todo)
description
usage: 1
TEXT
LENGTH(value)<=4096
(no default) Long text field type supported by view_base_class_2 (no todo)
email
usage: 1
VARCHAR, 64
value RLIKE '\w+\@\w+\.\w+' OR value = ''
(no default) email address type supported by view_base_class_2 (no todo)
ip_address
usage: 0
VARCHAR, 15
value RLIKE "([0-255\*]\.[0-255\*]\.[0-255\*]\.[0-255\*]])"
(no default) IP address type supported by view_base_class_2 (no todo)
number_dec42
usage: 0
DECIMAL, 4, 2
-100.00 < value <100.00
0.00 xx.xx numeric string type supported by view_base_class_2 (no todo)
number_double
usage: 0
DOUBLE
value is type double
0.0 Double precision type supported by view_base_class_2 (no todo)
number_int
usage: 0
INTEGER
value RLIKE '[0-9]{0,12}'
(no default) Integer number type supported by view_base_class_2 (no todo)
passwd
usage: 1
VARCHAR, 12
value RLIKE '[a-zA-Z_0-9]{5,12}'
(no default) Password type supported by view_base_class_2 (no todo)
short_description
usage: 0
TEXT
LENGTH(value)<=2048
(no default) A short text area domain <=2048 characters (no todo)
text_128
usage: 0
VARCHAR, 128
LENGTH(value)<=128
(no default) Text field 128, type supported by view_base_class_2 (no todo)
text_16
usage: 4
VARCHAR, 16
LENGTH(value)<=16
(no default) Text field 16, type supported by view_base_class_2 (no todo)
text_255
usage: 0
VARCHAR, 255
LENGTH(value)<=255
(no default) Text field 255, type supported by view_base_class_2 (no todo)
text_32
usage: 4
VARCHAR, 32
LENGTH(value)<=32
(no default) Text field 32, type supported by view_base_class_2 (no todo)
text_64
usage: 0
VARCHAR, 64
LENGTH(value)<=64
(no default) Text field 64, type supported by view_base_class_2 (no todo)
unix_time
usage: 1
INTEGER
value RLIKE '[0-9]{0,12}'
(no default) Unix timestamp type supported by view_base_class_2 (no todo)
url
usage: 0
VARCHAR, 255
value is valid url
(no default) URL type supported by view_base_class_2 (no todo)
us_dollar
usage: 0
DECIMAL, 12, 2
value RLIKE '^\d*\.\d{2,2}$'
0.00 US Dollar type supported by view_base_class_2 (no todo)
us_phone
usage: 0
VARCHAR, 14
value RLIKE '[\d\-\s]*'
(no default) US Phone type supported by view_base_class_2 (no todo)
us_state
usage: 0
CHAR, 2
value in (STATE_ABBR_LIST)
(no default) US State two letter type supported by view_base_class_2 (no todo)
us_zip
usage: 0
VARCHAR, 10
value RLIKE '(\d{5,5}(-\d{4,4}){1,1}'
(no default) US Zip code type supported by view_base_class_2 (no todo)
username
usage: 0
VARCHAR, 12
value RLIKE '[a-zA-Z0-9_]{5,12}'
(no default) User name type supported by view_base_class_2 (no todo)

^Top, Data Dictionary Index, Domains Index

Relationships

permission_user
user_email_addr
user_phone
user_user_history

^Top, Data Dictionary Index

Relationship name Parent / Child PK - FK Description TODO
permission_user
1:0+, Non-Identifying
permission
user
permission permission
Assigns permission level to user (no todo)
user_email_addr
1:1+, Non-Identifying
user
email_addr
user_id user_id
Relates email_addr to user (no todo)
user_phone
1:1+, Non-Identifying
user
phone
user_id user_id
Relates phone number to user (no todo)
user_user_history
1:0+, Non-Identifying
user
user_history
user_id user_id
Relates support event history to user (no todo)

^Top, Data Dictionary Index, Relationships Index