database

[Jim Herrmann]

Tim reid wrote:

>On 4/25/05, Jim Herrmann <kclug at itdepends.com> 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. 
>>    
>>
>I think so.  I would like to do something on an F/OSS database
>platform, maybe something that I could serve on a local LAN (but it is
>not required)  I would also like to be able to do this with software
>that is free as in beer. I would also like to keep it in a fairly open
>format (can you DB in XML?)
>  
>
MySQL is probably the way for you to go on this application.  It's OSS, 
dual license actually, but unless you are going to sell this application 
and use MySQL, you pay a license fee.  At least, I think that's the 
requirement.  Postgres, is under a BSD license, though, so you could 
check it out.

>>The first thing you need to do is create a logical model.  When doing a
>>logical model use English business names, and not programming
>>    
>>
>
>OK, I work with cell phones.  I would like to create a DB that I could
>record repair transactions in.  I would like to create a DB that I
>could search for the PTN(phone number), IMEI, SIM, Account # and
>Account name.  I would also like to have a field (say 50-100 char
>long) that I could write in a discription about the transaction
>
>  
>
You're already using acronyms, which is not English.  Newspeak, maybe, 
but not English.  Sounds like you would have an account entity and a 
phone entity, at least.  I don't know what those other two acronyms 
stand for.  They might be entities, or they might be attributes.  Not 
sure from the information provided.  In addition to the two entities I 
mentioned, you will also have something like a "repair" entity that will 
be a child of both phone and account, I think.  Now, an account would 
contain multiple phones, right?  Or it could anyway?  Does a repair 
belong to a phone or an account, or both?

>>Once you have this, you can create an Entity-Relationship (ER) diagram.
>> Now, if you find
>>an attribute occurs multiple times in an entity, then you should
>>"normalize" those attributes into another entity or entities. 
>>    
>>
>Well, each phone is going to have a PTN, IMEI, SIM that is uniqe to
>that phone.  But I guess that the PTN could be the top level
>attribute, and everything else (IMEI, SIM, Acct.#, Acct name) could be
>related to it.  I would also like to have some sort of widget that
>would search the DB from last month and let me know if I have filed
>for compensation on the repair, or if the repair has been paid for at
>the time of the transaction.  If it is a repair that I can file
>on...or one that I would have to file multiple times to get fully
>compensated, I would like the widget to tell me that I could do that
>"today" and give me the entries that I can file on.  (so i guess that
>i will have to have a way to flag a record(?) as paid/not paid)
>
>  
>
So you have some more attributes for your entities.  Some dates, amount 
fields, status codes, etc.

>> 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.)  :-)
>>    
>>
>Wow, that's the first database joke I've ever heard....Wow
>  
>
When you've been in the business as long as I have you've heard them 
all, although there aren't many of them.  :-)  And actually, this saying 
is more than a joke.  It's actually a rule of thumb to use when you are 
normalizing data.

>That's something of what I think I want to be able to do.  Am I headed
>in the right direction?
>  
>
It depends on how far you want to take it.  If others are going to use 
this database on a daily basis, and it's going to be an important part 
of your business, then you need to think through the logical design 
completely before proceeding with a physical implementation.  If it's 
just a learning exercise, then blast away at it.

>Also, can you give me some examples of normalization, indexes, keys,
>etc? 
>
Normalization is where you eliminate redundant data by making more 
entities and using relationships between those entities.  For example, a 
customer could have multiple accounts, right?  Now you could have one 
account record that repeats the customer information on multiple records 
for each account.  Then if the customer changes their address, you have 
to update the customer information on every account record.  Ugly.  To 
normalize that, you make a customer record and an account record, and 
establish a one to many relationship between them.

Indexes.  Say you have a table with a million rows, and you want to find 
one customer's records out of that million rows.  Now, do you want to 
have to read all million rows every time you look up a customer, or 
would you rather have an index on the customer number, so that the data 
base can pull back only the pages that contain rows for that customer 
into its buffers?  One will take 15 seconds, the other will be sub 
second.  Now when you start joining multiple tables, it becomes even 
more important to use indexes.  If your database only has 25 rows, it's 
a who cares.  Indexes would be a waste.  Large tables, gotta have em.

Keys are just unique identifier, like a phone number, or account 
number.  SSN is not unique, as there are duplicates, so is not a good 
key candidate.  That's a primary key.  Now, when a table has a child 
relationship with another table, the parent key is maintained in the 
child table, along with other key information so that you can have 
multiple children, and there it is called a foreign key.  When you 
create a relational data base relation ship, you create a foreign key on 
the child table that references the parent table.  A good data base 
management system (DBMS) will enforce that relationship so that you 
can't create a child row without there being a parent row.  Then, when 
you delete a parent row, the DBMS should either delete all the children, 
or fail the parent delete until you manually delete all the children.  
Sometimes there are other options as well, but that's the simple 
explanation.

> Also, what the heck is a DBA?
>  
>
Data Base Administrator, or Don't Bother Asking.  ;-)  Another data base 
joke.

>All in all, Thanks Jim
>  
>
You're welcome.  Hope I haven't gone too far off the topic of Linux.  
How about this?  These data base lessons, are provided free as in beer.  ;-)

Peace,
Jim


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