Looking for some very basic MySQL consulting (Paid Work)

[Jonathan Clark]

Hey everyone,

I know we have a few database guru's around the space and I'm looking for some basic guidance and assistance for a database I'm putting together for work.  It is really nothing more then a database of employee information but there are a number of relational tables and I want to make sure I have everything setup correctly before I start moving forward.  I am looking for someone with a lot of MySQL experience and preferable someone who is familiar with MySQL Workbench.  If anyone is interested please feel free to send me an email with an hourly rate. (I'll pay you cash up front for this)

[Jonathan Clark]

 Thanks to everyone that's responded,

At the moment I'm just creating the initial database and working on some very simple pages to display and input data.  I think I have the database structured correctly but then again this is my first time creating any type of database and I have less then 24 hours of experience, so I'm sure there errors all over the place.

Once I have got the majority of the database setup I am definitely interested in any guidance related to the best way to display data and create forms for data input. (I may also be able to pay someone to get the initial web interface up and running).

Here's a copy of the current database I've created and the relationship diagram.  If any of you database guys wanna take a look at it and let me know if there's anything I'm doing horribly wrong, please feel free.  I'll take all the advice I can get.  THANKS!

[Brian Wagner]

I tend to create a single codes table for 'types' and 'statuses' and other lookup type codes.  Then as you add functionality to your db, you can simply add another code instead of building another table.  Also consider using an n-tier database strategy using a module that handles all database calls.  You really do not want sql in your view code.  Finally you might need a timestamp field in your tables if you worry about multiple people editing the same record at the same time.  Most of the DB work I do is in Visual Basic and Sql Server but the concepts are the same.  You may want to read up on Ruby on Rails.  Good database strategy there.  There is a lot to being a DBA and a good database programmer.  Keep reading and ask me any questions.  I have been in this game for 20+ years.  Good luck.

Brian

[Jonathan Clark]

Brian,

I like the concept of using single status and type tables for tables that have the same status and type definitions.  You wouldn't use the same table to put ALL status definitions in though..right?  Only when the fields referencing the status values will share the same exact set of options?

I definitely missed the adding of a data/time stamp for entries!

 I don't know what an n-tier database strategy is, but I know how to start google-ing :) Thanks for the input!

[Essobi]

Normalizing.  Some of those fields can be enums.  Normalize if you need it to scale.  Do input sanitation too.  Lookup OWASP.

-essobi

--
Essobi

[Jonathan Clark]

Essobi,

I'm trying to figure out what fields could be normalized. After looking up normalization I think I understand the concept, and I think the database is structured according to that method, but I may not be fully understanding normalization.  Can you point out an example of whats not normalized?

[Jonathan Clark]

And here's another questions for you guys.

I completely understand the 1-many relationship and the need for this type of relation.  I think I understand the 1-1 relationship and just want to make sure I'm correct. 

Obviously the 1-1 relation means the primary table has a foreign key field that links to a single record in a related table. My question is regarding the appropriate times to use this?  Here is an example of where I think it's appropriate and what I understand as the reason for using it.   

My "main" table is a table that contains the basic details for each employee.  Along with the basic information we also need to enter in lots of additional data like: tax form information, emergency contact information, employee application information, etc.. 

This is a lot of data that needs to be associated with each employee but will be accessed very rarely in relation to the employees basic information.  I'm making the assumption that having ALL this data in one single table would create a lot of extra overhead when querying for just the basic employee info. (besides just being an un-sorted collage of random data in a single table, which my OCD doesn't like one bit). 

So the correct thing to do would be create a collection of seperate table to hold the different sets of data in, like: mng_employee_application, mng_emp_tax_data, mng_emp_emergency_contacts, etc..  and then link the records of each set of data back to the corresponding employee by means of the employee_ID primary key and 1:1 relationship.

Am I on the right track here? I'm looking at all the additional tables I need to start creation and just want to make sure I really am understanding this correctly before I move on.

Ohhh... one last quick question...  Would you put a datatime stamp field in every table or is there another method for managing multiple users entering data simultaneously?  

 THANKS AGAIN for all your guys advice!

[Jeff Johnson]

Jon, on the date thing, it depends if you want to do what is called "effective dating".  Effective dating allows you to keep a history of past changes.  Your page, or whatever you are showing the data with only shows the most active date that is <= current date.  This date HAS to be a key.  It can complicate your searches, because you have to say something like.

SELECT A.EMPLID, A......

FROM PS_EMP A

WHERE A.EMPLID='XXXX'

AND A.EFFDT = ( SELECT MAX(EFFDT)

                           FROM PS_EMP

                           WHERE EMP.EMPLID=A.EMPLID

                            AND EFFDT< SYSDATE)

The trick is, do you have each of your normalized/sub tables using this effective date logic, and if so, do you have individual queries (like above) for each of those, or do you use the same effective date for all of your tables (by same I mean like 5/1/2012) so you can join them easier with keys.  The latter is normally done.

BTW, the reason you look to make sure the EFFDT <= current date is because sometimes people like to future data a change such as a compensation amount.

Hope this helps.

[D.s.]

Normalization is SQL term for abstraction. (that's an analogy, not an exact definition)

So let's say you have a table that keep addresses of people.

Now you can record the literal string for the state and the city. But you are going to have lots repeating strings there. Since you are an SQL guru you are going to normalize the data. Instead of storing the string, you'll store an integer id that could be used to retrieve a record from your city and state tables to figure what data you are talking about.

Normalization is a trade off between size and speed. Every time you normalize a field your are adding a new table that you will have to join with later. Joins are pricy.

My personal policy:

Only normalize large repeating data sets. Don't normalize enums.

[John Coder]

as an example.  you have 90 employees. 50 of them are in zip code 40207, 40 in 40208. you enter the zip codes in with the regular address of the employee. that means 40207 is repeated 50 times in that table. Normalization would be making a separate table of zip codes with maybe id of 1 being 40207 and 2 being 40208. You want to query the table for all those living in 40208, the db has to expend resources looking at 5 digits in the first example as opposed to 1 digit in the second example there fore expending five times the resources. Simplified explanation.

[D.s.]

Yes, just don't overkill. You don't get penalized in SQL for having large tables. Shell you do but only during search. And that can be compensated for by using indices. Use analyze to figure out the complexity of your queries.

For the concurrency stuff... Well that really where the whole business rules/logic comes into play. You'll have to implement some row (because it's MySQL) lock management logic in your app logic. Or you could just say screw it and let nature sort it out.

For future, always design a db that deal with business finances to record the last time and by whom a finance related record was modified by. So you might consider first implementing a generic library for handling financial journal transactions with a modification log.

:) 

[Brian Wagner]

Another thing, if there is already a program that does what you want, use (buy) it instead of rolling your own.  Quickbooks is much cheaper than me having to write and accounting package.  You would be surprised what is out there.

Brian