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
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
On 4/25/05, Jim Herrmann kclug@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?)
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
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)
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
That's something of what I think I want to be able to do. Am I headed in the right direction? Also, can you give me some examples of normalization, indexes, keys, etc? Also, what the heck is a DBA?
All in all, Thanks Jim
Tim reid wrote:
On 4/25/05, Jim Herrmann kclug@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
Jim Herrmann wrote:
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.
Alternately, SQLite is in the public domain and has a relatively small learning curve.
Check here to see if it is right for your situation:
http://www.sqlite.org/whentouse.html
Regards,
Timothy A. Canon Community Resource Network Web Administrator 106 West 11th St, Suite 110 816.960.0708 x119 Kansas City, MO 64105-1806 email:tim@crn.org website:http://www.crn.org/
A good book for you to learn database design and SQL would be "The Practical SQL Handbook". I think the latest edition (3rd?) is a green colour. It is a simple read and will get you hooked up with the basics.
I also personally recommend PostgreSQL over Mysql, but that is just me.
On Mon, 25 Apr 2005, Tim reid wrote:
On 4/25/05, Jim Herrmann kclug@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?)
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
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)
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
That's something of what I think I want to be able to do. Am I headed in the right direction? Also, can you give me some examples of normalization, indexes, keys, etc? Also, what the heck is a DBA?
All in all, Thanks Jim _______________________________________________ Kclug mailing list Kclug@kclug.org http://kclug.org/mailman/listinfo/kclug
//========================================================\ || D. Hageman dhageman@dracken.com || \========================================================//
-
D. Hageman -
Jim Herrmann -
Tim reid -
Timothy A. Canon