Database Schema - Please advise........

Last Post 20 May 2006 09:26 AM by cmdr_jpskywalker. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
19 May 2006 01:56 PM
Ok, heres my situation. I've been tasked to design a db model (schema) for an in-house change mgmt system - Fair enough. But in this primitive environ, no business logic, flow, or specs has been provided. Just a form they used for paper-CM. Not much to go on, so I was hoping either:

1) You can make suggestions as to tables, fields, columns, function, etc.
2) Point me to a resource on-line
3) Or, provide me w/an existing db schema for such a system (Most desired )

OR, any help or advice on this humongous task, that they think is a snap for a DBA

TIA
cmdr_jpskywalker
New Member
New Member

--
20 May 2006 09:26 AM
1. First, find out the business objectives. Know is the model for operational reporting, operational transaction or a data warehouse. List them out so have a guide later on in making decisions.
2. From the artifacts that you have, create a preliminary object-role model or list all the entities.
3. populate the attributes on those entities.
4. identify the relationships between entities.
5. perform a business interview with people to ensure that you have a good/sound entity list and relationships. make sure you have them documented.
6. refine the logical model and let the key people (business analysts or IT personnel) sign off to what you have modeled. after finalizing the logical model, develop the physical model.
7. implement the design.
cmdr_jpskywalker
New Member
New Member

--
20 May 2006 09:28 AM
by the way, in step 2, identify the business process so that you can identify the entities or business objects. document them. as a principle, the business requirements drives the technical solution, not the other way around.
SQLUSA
New Member
New Member

--
20 May 2006 10:13 PM
Start with the form. Design the tables.

Talk with a few people, add more tables.

List them all here.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com/bestpractices2005/top10/
The Best SQL Server 2005 Training in the World
SQL_Jr
New Member
New Member

--
21 May 2006 09:09 AM
thx guys..........i will post back here - hope you keep an eye on this thread
SQL_Jr
New Member
New Member

--
22 May 2006 07:46 AM
hi, all:

here's an update, and hope for some guidance. So, I started drafting stuff, and I see there are categories of tables needed (w/o listing them for now):
    Lookups
    Dynamic/Current
    History
. I'll keep hist for later.

Keepin to the rules of normalization, based on the lookup tables, and say generating a "ChangeMgmt Ticket", I would need to create a ChangeMain table, which I guess would be a junction table, cotaining the ids of the other lookup tables (Is this right?)

My main question is, what should the ChangeMain table look like? I would need ChangeNo,ChangeDesc,ChangeReason as fields just for that table. How should I break up this table? thc
GoldenGal
New Member
New Member

--
23 May 2006 07:35 PM
Hey SQL_Jr,

You've already got one of the most important requirements documents you need in your hand -- the Change Management paper form. For a business process analyst, data modeler, or data architect, this is pure gold, and the basis of the requirements docs that will lead to your Change Management system.

However, if your skills lean more to the DBA than a DA, this might not be much help to you. And, if your management fails to understand that there's a world of difference between DBAs and DAs, you have little hope of help from that quarter. Sadly, your management is not alone in this limitation.

I don't know of any pre-existing db schemas specifically for change management, but I do know that there are sets of published schemas available on all kinds of topics, some free, some for sale. Use your favorite web search engine, type in "database schema" and see what you can find.

Good luck, and have fun!

cmdr_jpskywalker
New Member
New Member

--
25 May 2006 07:54 AM
As Michelle said, start with the documents/forms/artifacts on hand. Unfortunately, most management personnel expect DBA to be a DA, Business Analyst and/or System Analysts. That means, you need to know more about the business. I guess, it comes with the territory. How can you maintain a database and tell a developer not to change the client table not unless you know about the client table and its business impact? Going back to your situation, start with 1, 2 and 3. For instance, you have that form, know the business process that triggers or being used. Then, try to identify the different objects being captured on that form (Change event, Management, Ticket-- 3 object to start). You may refine it later on as you move along. But if you're looking for a template to have an idea, use the internet to search for models.

Hope this helps .

The path of the one is difficult, that is, the one to blame , jk.
You are not authorized to post a reply.

Acceptable Use Policy