Do I really need to change my naming convention?
-
Do I really need to change my naming conventions for the following tables?
Some of the things i'm concerned about are
- the abbreviations i used. All of which i like! Mostly occurs in keys
- attributes with same name but different tables such as LastUpdated
(I don't see this as a particular problem but i've read that i shouldn't do this)
- attributes with similar names such as Comment and CommentSection
- Underscores in two table names
To me, being entirely consistent would be unnecessary and could cause more harm than good. But i'm open for suggestions.
Thanks!
Employees
EmpID* Primary key
FirstName Employee’s first name
LastName Employee’s last name
BadgeNumber Employee’s badge number or number on check stub for salaried employees without badge numbers
DeptID Foreign key from Departments table
CenterID Foreign key from Centers table
Table Description:
Stores vital information about Employees
Centers
CenterID* Primary key
Center Name of each Franklin center
Table Description:
Serves as a lookup table for all of Franklin’s centers
Departments
DeptID* Primary key
Department Name of each Franklin department
Table Description:
Serves as a lookup table storing all of Franklin’s departments
Tests
TestID* Primary key
TestName Name of test or quiz
Table Description:
Used to identify all available tests or training quizzes.
Categories
CategoryID* Primary key
TestID Foreign key from Tests table
Category Category’s name
CategoryPurpose Description of the purpose of each category
Table Description:
Provides each exam category with a unique identifier and name. It also stores a description of the category’s purpose.
Resources
ResourceID* Primary key
CategoryID Foreign key from Categories table
ResourceTitle Official title of the file, video, manual, etc.
FileName Address or file name and extension
FileType File type (such as PDF, AVI, MP3)
LastUpdated Date material was last updated
Table Description:
Stores all complementary resources used during training.
Learning
LearningID* Primary key
CategoryID Foreign key from Categories table
LearningObjective Learning objective
LastUpated Last time objective was updated
Table Description:
Stores the specific learning objectives for each category
Questions
QID* Primary key
CategoryID Foreign key from Categories table; identifies which questions belong to which category.
Question Exam question
References Manuals and page numbers
LastUpdated Last time question and related info were updated
Table Description:
Provides a pool of questions for each exam category.
Answers
AnswerID* Primary key
QID Foreign key from Questions table
Answer Possible answer to a particular question
Correct Boolean value describing whether answer is a correct choice or not.
Table Description:
Stores all the possible answers to a particular question.
Diagrams
DiagramID* Primary key
QID Foreign key from Questions table
FileName Name of diagram file; used to find file on disk or server.
DiagramDesc Description of file
LastUpdated Last time diagram and/or diagram info was updated
Table Description:
Stores information on diagrams used on the test.
EmployeeTests
EmployeeTestID* Primary key
TestID Foreign key from Tests table
EmpID Foreign key from Employees table
EmpTestStartDate Date employee test began
EmpTestFinishDate Date employee test was completed
Table Description:
This table stores all current and completed testing or training sessions for each employee. It is used to resolve M:N relationship between Employees and Completed_Cateogories tables or Employees and Tests tables.
Completed_Categories
CCID* Primary key
EmployeeTestID Foreign key from EmployeeTests table; used to identify which completed categories belong to each employee test.
CategoryID Foreign key from Categories table; used to identify tested or completed category.
StartTime Stores the date and time testing of objective began.
FinishTime Stores the date and time testing of objective was completed.
CommentSection Additional comments made by computer or other user regarding the status of a particular completed category.
Table Description:
Used to store all categories completed by user. Ties completed categories to a specific employee test, allowing for retesting.
Completed_Questions
CQID* Primary key
QID Foreign key from Questions table
CCID Foreign key from Completed_Categories table.
Comment Employee’s comments for questions that allow this option.
Table Description:
Stores all the test questions completed by employees
Responses
ResponseID* Primary key
CQID Foreign key from Completed_Questions table
AnswerID Foreign key from Answers table
Table Description:
Selected answers or choices from the Answers table.
-
Apart from the table names being plural (I think most DB developers tend to stick to singular), your naming convention looks absolutely fine to me. Having a 'LastUpdated' column in several tables makes a lot of sense to me - you always know what this field is doing. I guess you could call it 'tablename'LastUpdated, but I really don't think that's necessary.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Looks good to me also. The only thing I might suggest is to preface the table name with a notation like tbl for readablity.
glenn
-
Thanks very much guys!
-
My 2 cents...I learned from a db design savant many moons ago to name my primary key columns - with an ID suffix and the foreign key columns with an FK suffix....so I would have "EmpID" when it's a PK and "EmpFK" all other times....
This way I can distinguish between the two at a glance....I must also say that I have not seen anyone else implement this....but then it's a huge world out there and I don't know everyone...
**ASCII stupid question, get a stupid ANSI !!!** -
CREATE TABLE [ObjADPSUsage] (
[PkObjADPUsage] [int] IDENTITY (1, 1) NOT NULL ,
[FkObjADP] [int] NOT NULL ,
[DateUsage] [datetime] NOT NULL CONSTRAINT [DF_ObjADPSUsage_DateUsage] DEFAULT (getdate()),
[FkUser] [int] NOT NULL ,
CONSTRAINT [PK_ObjADPSUsage] PRIMARY KEY CLUSTERED
(
[PkObjADPUsage]
) ON [PRIMARY] ,
CONSTRAINT [FK_ObjADPSUsage_FkObjADPS] FOREIGN KEY
(
[FkObjADP]
) REFERENCES [ObjADPS] (
[PkObjADP]
),
CONSTRAINT [FK_ObjADPSUsage_FkUsers] FOREIGN KEY
(
[FkUser]
) REFERENCES [Users] (
[PkUser]
)
) ON [PRIMARY]
GO
Ya I know I don't need a PK there... I just haven't got time to redesign my system to apply all the knowledge I acquired here
. -
- I'm going to change that to - "what a small world this is...." - thx. for confirming ...nice to know (at least) one other person..
**ASCII stupid question, get a stupid ANSI !!!** -
... and everyone else who followed the same course as I did so that would be a few hundreds
. -
That is an excellent methodology. Thanks
I wasn't born stupid - I had to study.
-
Yay! A convert!
**ASCII stupid question, get a stupid ANSI !!!** -
Here's the rest of mine if anyone's interested :
CK : check constraint
DF : default constraint
FK : Foreign key --for objects and columns
PK : Primary key --for objects and columns
fn : Scalar function
fnvw : Inline/table function
SPN : Stored proc
IX : Index
IXUQ : unique index constraint
vw : View
Note that I have nothing for tables. This is because if I do Select from ?? and it's not fn, fnvw or vw then I know it's a table. Makes less visual noise and it's still almost impossible to confuse.
-
We use a 3-part naming standard for data elements.
part 1 - Entity prefix acronym or abbreviation of the logical entity or physical fil that the element is an attribute of.
part 2 - Descriptive keywords word or group of words that accurately describes what the element is. Abbreviated if necessary. May be omitted if the entity prefix and class keyword sufficiently describe the element.
part 3 - Class keyword abbreviation of the data element type that describes what type of data the element is.
Using this standard a customer's mailing address would be CUST_MAILING_ADDR or CUSTMAILINGADDR; and employee's badge number would be EMP_BADGE_NUM or EMPBADGENUM.
If a data element is part of a foreign key, it retains the entity prefix of the table it originates in.
Greg
Greg
-
Where I am now, they do not use vw and that is really tough when you are starting to recognize an actual table is not being used.
The SPN part I no longer use since they are stored independently and calling them is generally pretty obvious.
I do not know what and Inline/Table function means? Could you explain?
I wasn't born stupid - I had to study.
-
It's a function that returns a table. Either by a straight select, or by declaring a table variable and returning that as the select. This gives you a chance to have a view with dynamic parameters just like a stored proc. The table function has also the advantage of being able to declare variable and make some pretty advanced business logic.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply