PDA

View Full Version : Database design


Iowanian
08-01-2006, 10:31 AM
I'm not an IT guy, I'm not a DBA, but I find myself designing Databases. Sometimes Access, Sometimes SQL.

My first question is, does anyone have a link to an easy to follow(plain english) resource to help decide which Data field type should be used?

Example...
I'm designing a db. I know I'll have a certain number of columns. Lets say, one is customer name, Address, City, Zip.

An example of where I get in trouble sometimes, would be a column that has to maintain a certain number of decimals, and be in a format that another software will recognize as a number.

What inevitably happens is that I enter data, lets say X,Y coordinates, and when I go to do the next function of my task, it doesn't read them correctly, or I lose some of the important decimals.

Is there a "cheat sheet" out there?

HC_Chief
08-01-2006, 10:40 AM
If you have SQL Server, use Books Online! If not, go to the microsoft web site and find Books Online. It has reference to all data types.

For numeric values w/ dcimals, I typically use DECIMAL(n,n), or FLOAT - depends on level of accuracy required.

For character values I always use VARCHAR, which is a variable-length character datatype. A value of 2 characters within a VARCHAR(255) column will use up exactly 2 charcters of space (8 bytes). That does not mean you should make ALL columns VARCHAR(255) - you should still try to figure out max length beforehand and size accordingly, but it is a quick & dirty way to handle.

HC_Chief
08-01-2006, 10:43 AM
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

StcChief
08-01-2006, 10:52 AM
No real cheat sheet. Different databases and front-end products do things differently to data (unforunately for the user). Based on how they were stored in the tables with data type chosen

Good rule of thumb
Use the native data type of the data.
Store numbers (real/integer) don't store in strings.
Strings in sting fields
Dates (if possible DATE datatype like Oracle has)
Pictures, binary files etc in BLOB/CLOB data type

Database design and data model is art/skill used to build a database for your business problem.
A good book to start with: Graeme Simsion Data Model Essentials
[url]http://www.amazon.com/gp/product/1576108724/102-0812748-5625710?v=glance&n=283155[/rul]


Several google links when searching for Data model will
help get you started.

Good luck.

yunghungwell
08-01-2006, 11:08 AM
Access solutions (http://www.databasedev.co.uk/index.aspx)

A good source if you are just starting out with Access databases.

Be sure and read some of the sections about normalizing the database to at least get an idea before just jumping in. It could save some trouble later.

yunghungwell
08-01-2006, 11:12 AM
Also, it took me a few screw-up's before I got the numberical data to work for me in Access. (ie The correct number of decimal places.)

Make sure that you figure it out before you get a bunch of data inputed into tables because you could end up with some data loss. I had a table that had a field formated incorrectly. Then when I changed the format to the desired format all of my numbers were rounded. Thank goodness I had someone who could help me reinput all of the data.

Iowanian
08-01-2006, 11:18 AM
Thats what happend. I had created a bunch of data from scratch (starting x and y coords and ending x,y) associated data etc......I have created several from scratch in the past in Access, and have built views from existing SQL tables, but only at an intermediate level.

What happend was, I had created/entered all the data, and when I went to bring it into another program to do "the guru stuff that I do", it didn't let me access the Coordinates because the field data type was incorrect.

I had manually converted from one form of coordinate to another, and entered those into these columns. I made an elementary error in judgement, due to a huge time constraint(which blew up in my face due to this)....without backing up the db like a dumbass....I changed the data type, and in access you have to "save".....and lost all the numbers behind the decimal....which in turn....fooked me.

Turns out, I've got alot to learn.

I'm also in the middle of a large DB design where I'm taking an existing program/db, and am to modify it to meet our needs. I have to change and create some forms, and make some lookup tables and integrate them into the existing Db.

It would sure be nice to have some dba support......

StcChief
08-01-2006, 11:50 AM
Thats what happend. I had created a bunch of data from scratch (starting x and y coords and ending x,y) associated data etc......I have created several from scratch in the past in Access, and have built views from existing SQL tables, but only at an intermediate level.

What happend was, I had created/entered all the data, and when I went to bring it into another program to do "the guru stuff that I do", it didn't let me access the Coordinates because the field data type was incorrect.

I had manually converted from one form of coordinate to another, and entered those into these columns. I made an elementary error in judgement, due to a huge time constraint(which blew up in my face due to this)....without backing up the db like a dumbass....I changed the data type, and in access you have to "save".....and lost all the numbers behind the decimal....which in turn....fooked me.

Turns out, I've got alot to learn.

I'm also in the middle of a large DB design where I'm taking an existing program/db, and am to modify it to meet our needs. I have to change and create some forms, and make some lookup tables and integrate them into the existing Db.

It would sure be nice to have some dba support......

I have done that as a time and material in the past ... It can be done remotely.
or with minimal meetings design doc exchange.
Other firms local (in Iowa may exist) if you look around, I doubt it will be cheap.

Iowanian
08-01-2006, 02:13 PM
Outsourcing is not an option.

I'll just have to figure it out and make it work.
I know I can easily make tables for picklists with things like manufacturers, Vendors, contractors et al....its getting them to work within an existing DB and template/form.

I'll do a little more reading on hungwell's site when I get some time. I'd like to find a "rule of thumb" in plain english, essentially what the "Default" choices should be for different data types.

I've heard for example, not to use "number" unless it was a field that would be in a formula or sum. The problems I've had in these issues is for example.......I have an 10 dig ID number....that some begin with "0", if I choose number as the type, it will drop the zero, which fooks me when I later Link that data to other tables/files.

yunghungwell
08-01-2006, 02:24 PM
I've heard for example, not to use "number" unless it was a field that would be in a formula or sum. The problems I've had in these issues is for example.......I have an 10 dig ID number....that some begin with "0", if I choose number as the type, it will drop the zero, which fooks me when I later Link that data to other tables/files.

That sounds like something that is entirely plausable. Make sure that what ever program that is using the data can either understand it as text (or whatever) or convert it back to the correct number format before using it. I have run into that problem on a more simplistic level where I was trying to use the data in Excel. When I needed to perform a mathematical opperation it didn't work because the "numbers" weren't numbers.

ck_IN
08-01-2006, 02:27 PM
Iowanian what have you gotten yourself into?!!

I charge large amounts of money to do this very thing and I've been doing it for years. I don't know your background but it sounds like your employer has thrown you in the deepend. Unless you're self employed and trying to save some cash.

Some basics are: Only store numbers as numbers if you're going to do math with them. Your example of the leading 0 is a good example. Also number datatypes take more room then a comparable varchar.

In your example, customer name, Address, City, Zip, all of those should be varchar.

I don't quite understand this example: where I get in trouble sometimes, would be a column that has to maintain a certain number of decimals, and be in a format that another software will recognize as a number. A number can store decimals if it's defined with that precision. For example a number(4,2) stores two pre-decimal and two post decimals. There are conversion functions to change varchar to number but if you're feeding input into another piece of software then you'll need the layout of the destination tables so you can do the data mapping. Without something like that then you're spitting in the wind.

StcChief
08-01-2006, 02:28 PM
Outsourcing is not an option.

I'll just have to figure it out and make it work.
I know I can easily make tables for picklists with things like manufacturers, Vendors, contractors et al....its getting them to work within an existing DB and template/form.

I'll do a little more reading on hungwell's site when I get some time. I'd like to find a "rule of thumb" in plain english, essentially what the "Default" choices should be for different data types.

I've heard for example, not to use "number" unless it was a field that would be in a formula or sum. The problems I've had in these issues is for example.......I have an 10 dig ID number....that some begin with "0", if I choose number as the type, it will drop the zero, which fooks me when I later Link that data to other tables/files.

The infamous Business use of strings for our 'natural' keys
e.g.invoice number left padded with '0' s to Fill the 10 digits. They want all sequential to ensure they didn't lose an invoice...
Serveral ways to solve that problem without build that intelligence into the key.

In general relational design tends use a 'surrogate key' to link tables.
These are NOT ment for business use only to guarantee unique rows in the table. and link related tables. Part of relation database design.....explained in books.


The rule of thumb here... It depends on your data and it's use. in your which RDBMS your using.


good practice the Tables should not depend on business natural keys
but use their own to ensure uniqueness. See definition of Primary key.

Iowanian
08-01-2006, 03:10 PM
Edited to protect Iowanian's employement from dumbarses.

Iowanian
08-01-2006, 03:12 PM
In another project, I have a coworker who has a large dataset of 3 basic types, that each have multiple attributes, test results, locations, etc...some have many to one, one to many relationships.

They have located a "free nationwide template" DB.....within that db template, I have to create lookup tables, to keep the data uniform (we all know how some data entry people work).


CK....In short, to answer your question.....I often ask myself that very question..."Iowanian, wtf have you gotten yourself into today".

It usually is preceeded by someone asking something beginning with my least favorite phrase in the workplace

"Iowanian, Could you just..........."

It doesn't matter what the rest is, its not going to be good.

Iowanian
08-01-2006, 03:37 PM
Since I'm asking fundamental IT questions.....

I have dual monitors, powered by Nvidia.

When I click somethign that opens a video clip, along with the window I click that opens.....it also opens full screen on the right screen.

how do I make that not happen?

StcChief
08-01-2006, 03:49 PM
sounds like package Mapinfo, ARCinfo.
The GIS spatial isn't running on Oracle is it? (doubt it)
Their NUMBER data type allows precision,scale values.

To stop data gettting munged truncated...I guess test more to ensure that it doesn't happen.....I hope you found a data type that stores real numbers.....


The other big project design with 1:M M:M relationships. Sounds like a good book would help.

To classify end user data (if you must give them white boxes to type in). Have a classicication drop down they must pick a value from at
least you can roll that up (summarize) later.....

Users...Can't live with them. Can't shoot them.

Iowanian
08-01-2006, 03:54 PM
No Oracle...bleeeeeeeech.

Everything I deal with is SQL or Access or a format called Geodatabase.

the stuff in question is created solely in the Database(access) or sql....and then I have to bring it in to use it (which is actually my job)

Helping them create the data and building these is just a "job benefit" of my type of employment.

StcChief
08-01-2006, 04:03 PM
SQL Server has actually improved in the last release....Has alot more built in functionality.

Access should integrate with it allowing you to centralilze data and scale up analysis. the data loss issue was from Geodatabase. I guess.??

For smaller SQL DBs and less concurrent users it works fine.

Priced a little better four your needs. Though Oracle is
trying to go after that smaller market.

Good luck got any DB Design question/concept design issues ping me. I'll Look at it Gratis.

Iowanian
08-01-2006, 04:06 PM
I wasn't to that point yet...It was all in Access, and mostly lost due to me breaking my 1st rule of fightclub......always make a copy before you dick with a file. They're changing the next version of the GDB to a file based db that will be alot better.

They had a seat of Oracle here.....that never was taken out of the box. It really wasn't necessary for what we do.


Thanks for the input....I apprecaite it. I'd prefer anything any more in depth about what I'm doing be PM anyway.