database

[Jim Herrmann]

Creative Commons baby!

Steven Danz wrote:

> Wow... you mind if I borrow this?  I'll reference you of course.   I 
> used to do the DBA thing long ago, and I've been trying to explain 
> some of these same things to some DB newbs, and just couldn't get it 
> to come across this clearly.
>
> Thanks for your time.
>
> Steven
>
> Jim Herrmann wrote:
>
>> Tim,
>> It sounds like you are wanting to do a fairly simple database 
>> structure, which is a good way to get started.  When you say "open 
>> database format", I assume that you are talking about a target 
>> platform of a relational database, something like MySQL or Postgres.  
>> Yes?  But that's getting ahead of the design curve.  Here's how to 
>> proceed if you want to follow something akin to a formal database 
>> design process.  I've been doing this for about 18 years as a DBA.  I 
>> hope I'm not volunteering too much information.  You said you were a 
>> newb.  :-)
>>
>> The first thing you need to do is create a logical model.  When doing 
>> a logical model use English business names, and not programming 
>> abbreviations.  Identify all the *Entities* that you need to 
>> represent, and the *Relationships* between those entities.  A entity 
>> is a singular noun, which would include things like "Person", 
>> "Department", or in your case, "Reminder".  A relationship is a verb 
>> phrase like "has many", "has one", or "has zero or many".  So, you 
>> can say something like "a Department has many Persons" and "a 
>> Department has one and only one Manager".
>>
>> Once you have this, you can create an Entity-Relationship (ER) 
>> diagram.  This gives you a very high level view of your data 
>> structure.  This would be a box for each entity and a line between 
>> the boxes represents a relationship.  Exactly what the box and line 
>> looks like depends on the modeling tool you use.  Then you need to 
>> identify all the attributes (fields) for each entity.  You need to 
>> identify which attributes uniquely identify an occurrence (row) of 
>> that entity, and that's your natural key.  Keys of parents, of a 
>> relationship, migrate to the children.  If the parent is required for 
>> a child to exist, then it is an "identifying" relationship, and and 
>> the parent's keys become part of the child's keys.  If it is a 
>> "non-identifying" relationship, then some or all of the parent key is 
>> NOT part of the child's key.  Now, if you find an attribute occurs 
>> multiple times in an entity, then you should "normalize" those 
>> attributes into another entity or entities.  There are several levels 
>> of normalization, and I won't get into it here as it would be a full 
>> day class, but third normal form is considered the best level of 
>> normalization for most DB designs.  That's where each attribute of 
>> each entity is dependent upon the key, the whole key, and nothing but 
>> the key, so help me Codd.  (E.F. Codd, an IBM fellow, created 
>> relational database theory.)  :-)
>>
>> Once you have a fully attributed ER diagram, you are ready to create 
>> a physical model based on the logical model you created.  This is 
>> where you create names that you want to use in your program, as you 
>> turn entities into *tables* and attributes become *columns* and 
>> relationships become *foreign keys*.  You might shorten "Department" 
>> to "DEPT", for example.  It's best if you use standard abbreviations, 
>> meaning be consistent, as much as possible.  It makes programming 
>> much easier.  The length of table and column names may need to be 
>> shortened, depending on the target database.  That's part of the 
>> physical design process.  You also need to decide what indexes will 
>> be on your tables.  Some for uniqueness, such as on the key, and some 
>> for performance, depending on how you are going to access your data.
>>
>> There are also performance and convenience tricks that you can do 
>> when you take your model physical.  For example, if you have some 
>> natural keys that have lots of columns, you might introduce 
>> artificial keys, which would be something like an integer or reverse 
>> timestamp.  You may also at this point decide to "denormalise" some 
>> entities to make your system go faster, but that comes at a price of 
>> maintaining redundant data, and it breaks Codd's rule.  Depending on 
>> the DB platform, there are lots of physical implementation issues to 
>> consider, if you are building something with lots of data or lots of 
>> transactions, but you probably don't need to concern yourself with 
>> all that for this project.
>>
>> Hopefully you find this information helpful.  If you have any further 
>> questions about data base, I'd be glad to help.  Sorry about the 
>> brain dump.  ;-)
>>
>> Enjoy,
>> Jim
>>
>> Tim reid wrote:
>>
>>> I'm thinking of programing/writing/whatever a database to keep track
>>> of some information for work.  I think that I would like to keep track
>>> of 7-12 fields of information, and be able to set up reminders that
>>> are tagged to date information.  For instance, "you did such and such
>>> 30 days ago...you can now file the claim, here is the information that
>>> you recorded earlier."
>>>
>>> How would i go about doing this, free as in beer, in an open database
>>> format?  Let me also mention that I'm a total newb  ;)
>>>
>>> TIA,
>>>
>>> Tim
>>>
>>>  
>>>
>>
>
>
>

-- 
Progressive Values ARE American Values
Responsibility, Empathy, Freedom, Opportunity, Prosperity, Fairness,
Trust, Honesty, Open Communication, Community, Cooperation
http://musicalprogress.org/