December 28, 2010 at 7:28 am
I am a new DBA with no mentor, not even 8months into this job. I am a mechanic by trade and have been for 20 years I understand maintenance, tho I am learning DBA. I have read 3 books so far at the urging of several industry experts. (DBA RockStar, SQL 2008 Administrator in Action and PowerShell user manual, this last one is still in progress.) I am a regular lurker in several forums and am trying to sort my way through this newly found field. Everything I have read suggests that I need to beginning my database project right, by setting up the schema and getting the requirements of the database understood. I guess this is where I need help. I think I have a solid understanding of what my project will entail, however I am not sure I understand the schema part, and how to go about developing it.
I am in the beginning stages of developing a database project to introduce MS SQL Server to my company. The vendor has a book of task for repairing aircraft and the tasks have personnel requirements, time requirement and special tool required to perform each task.
My idea is to place all tasks per manual(8 manual and about 500 tasks) in a table, all special tools (about 150 tools)in a table and then and then assign values to the personnel like, engine mechanic, body specialist, transmission guy and so on. So for example;
Task: Transmission Removal : 2 Mechanics 2.0hrs, 1 Electrical .5 hrs. Tools 3 wrenches, 1 Lift, 1 Transmission Stand.
Then I was planning on building a form to present to a subject matter expert(SME) so he/she could view the task and then assign what he/she though was needed to complete the task and how long. The results from each task would get recorded on a separate table created by each SME and stored for later review.
In the end a report of the total of all the SME results would then be calculated in a table and then used as data for a report. The information would be used, not a direct output from the table.
So in a nutshell I have to build tables for each component, tasks, personnel, tools and times. Build a results table to catch all of the data being reviewed. Where does the schema design for the database come in?
December 28, 2010 at 8:33 am
That is a schema design for the database. Well, it's the outline of one (logical design).
schema
Definition
sche·ma[ sk?m? ]
sche·ma·ta Plural
NOUN
1. diagram: a diagram or plan showing the basic outline of something
2. mental pattern: an organizational or conceptual pattern in the mind
-- Encarta/Bing Dictionary
It's really as simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2010 at 8:45 am
Short answer, right at the beginning.
Just be careful with the nomenclature. Schema can mean the database design and it can mean the owners of the objects within the database. I'm assuming you're refering to the design.
You're diving into the deep end of the pool to start a database design without prior knowledge. Good luck.
In general, go with the approach you've taken, identify the nouns and assume they're tables. Identify the properties around those nouns and assume they're columns on the tables. Figure out the relationships between the nouns and assume they're the foreign keys. Be careful about identifying the natural keys for the nouns, the thing that uniequely identifies them. You need those whether you decide to go with artificial keys or not. Also identifying the clustered index (possibly the primary key, but not absolutely) is an important part of the initial design.
In short, it's a huge task. If you can't bring in someone to help, just assume you're going to mess up a few things and have to rework. Make it part of theplan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 28, 2010 at 11:24 am
Mrfixit1170 (12/28/2010)
I am a new DBA with no mentor, not even 8 months into this job.
I also understand your question refers to database design - if this is the case you are correct, as times goes on you will be able to trace down to initial database design either the reason of your success or the reason you are fixing issues at 2am, or in an extreme case the reason you have lost your job.
Lets start with the correct words. The first thing you have to come out is with a "data model" usually referred as the "ER model" ER=Entity/Relationship.
Once you have a "data model" that truthfully represents your system you hav to work on "data normalization", I'll say you will have to normalize your data to the Third Normal Form a.k.a. 3NF.
Finally you go with your physical implementation which means you will physically create tables and indexes matching your 3NF model
So, bottom line is: research "logical model", "data model", "ER model", "data normalization" specifically "3NF".
Once you get there post specific questions, we cannot do your job but we can certainly bounce ideas and point your nose in the right direction.
Good luck! 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 28, 2010 at 11:49 am
GSquared, Grant and PaulB,
Thank you for your input to my very long winded question. I understand that this is a rather large project but I am a "Feet First, the waters fine" kind of problem solver. My question involving the schema and where it fits into my project I guess I was overthinking what it was. I was reflecting back on a paper concerning how xml doc's were structures and the structure was refered to as the schema. I was thinking the database design had to follow the same structure. (I'm sure it does but not so linear)I am sure I will have question to follow. So thank you for the encouraging words concerning follow-on questions.
December 28, 2010 at 11:53 am
You're welcome.
Database schema are similar to XML schemas, in that they define the data that will be held. The difference is, in a relational database, the "schema" IS the tables and columns. There's also a sub-division for tables called "the schema", which can be confusing because it means a similar but slightly different thing. (Like much of the English language, the same word can mean two or more entirely different things, even in the same field.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2010 at 12:25 pm
You are welcome.
Just as a foot note let me add that it is imperative to have a sound data model before jumping into building tables.
Step #1 would be to identify what "entities" are part of your model like: TASKS, TOOLS, PARTS, PERSONNEL, etc.
Step #2 would be to identify which "relationships" link(1) your "entities" like:
TASKS consume PARTS, PERSONNEL perform TASKS, etc.
Step #3 would be to normalize your data but first, you have to have a sound ER model.
Step #4 would be the creation of your tables and basic indexing strategy based on your normalized logical model.
Please do not take my above definitions of entities and relationships at face value, they are only there as an example and may not be the ones that are needed for your project.
--
(1) Edited: link x like
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 28, 2010 at 1:06 pm
GSquared, Thank you for the clarification, I have a better understanding now after seeing your example.
PaulB, Your reference example as to establishing the structure, helps out lots. I guess I was working backwards trying to envision what the user was going to see and building the design backwards.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply