Suggest data type

[rAinDeEr]

Hi,

I have a table with the following 2 Fields - one is the description
name and the second is its description to be stored in a table.

description name description
------------------------------ ------------------
Terms and Conditions ( to store large amounts of text, Dont know
which data type to select)
Attachment limit (integer)
Max certificates (integer)
message 1 (large amount of text)
message 2 (large amount of text)
message 3 (large amount of text)

Sample structure is like this ::

Create table parm( parm_descriptio n varchar(25),par m_txt clob(1 M))

is the one I thought of.

But attachment limit and max certificates is integer value and storing
this in a 1M clob will be wastage of space..

Can any one suggest some thing else...(how to split the table)?

Thanks in advance,
rAinDeEr

[Rhino]

"rAinDeEr" <ta**********@g mail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

Hi,

I have a table with the following 2 Fields - one is the description
name and the second is its description to be stored in a table.

description name description
------------------------------ ------------------
Terms and Conditions ( to store large amounts of text, Dont know
which data type to select)
Attachment limit (integer)
Max certificates (integer)
message 1 (large amount of text)
message 2 (large amount of text)
message 3 (large amount of text)

Sample structure is like this ::

Create table parm( parm_descriptio n varchar(25),par m_txt clob(1 M))

is the one I thought of.

But attachment limit and max certificates is integer value and storing
this in a 1M clob will be wastage of space..

Can any one suggest some thing else...(how to split the table)?

Thanks in advance,
rAinDeEr

You haven't explained your situation very clearly so there is no way that I
can critique your full design. Frankly, I think you need to find a tutorial
or course that teaches you database design, particularly normalization of
data. I've seen some tutorials like this online; none of them have really
impressed me but some of them might be good enough to get you started. Of
course, it would be even better if you hired a professional database
designer to help you.

It's just not possible to give you a more detailed answer without asking you
a lot of questions and getting detailed answers. I can't afford to give away
that much time for free and I doubt anyone else could either.

There are simple techniques to dramatically reduce the amount of data that
needs to be stored in a database but I am reluctant to describe these
techniques without knowing whether they will be beneficial in your
particular case. I can't tell if they would be beneficial without getting a
number of involved questions answered. Also, if you don't understand the
proper use of these techniques, it could do you more harm than good.

All I can really do at this point is repeat that you should talk to an
experienced database designer and follow his/her advice or learn database
design yourself. I've seen good three day courses on database design that
would teach you much of what you'll need to know. That might be cheaper and
more satisfying for you than hiring a database designer. Then again, if you
are in a hurry, it would probably be faster to hire a competent designer and
follow his/her recommendations .

If you'd like to hire a competent database designer for a reasonable fee,
post again with information on how I can reach you offline. We can discuss
this a bit and maybe reach some terms that we can both live with.

--
Rhino

[Mehmet Baserdem]

With all due respect to What Rhino says. My humble suggestion is using
a second table for your message records with integer message_ids and
CLOB message texts. Use message_ids as foreign key in the first table
description field.

Regards,

Mehmet Baserdem

[rAinDeEr]

Hi Rhino,

Guess I have to get hold of some materials..or join a course as u
suggest..Coz i am in not a position to hire..I am asked to perform
these designs as a part of my job..I am a starter in these and when
ever I have any doubt I would post here..I started as a DB2 UDB DBA and
now juggling between Erwin/UDB/Documentation/Testing..

And I havent found any relevant material which would help..Also suggest
some online course/books (If u have time) so that I can LEARN Database
designing..Coz I want to concentrate on DBA as well as modeling
activities..

thanks a lot...
rAinDeEr

[Rhino]

"rAinDeEr" <ta**********@g mail.com> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

Hi Rhino,

Guess I have to get hold of some materials..or join a course as u
suggest..Coz i am in not a position to hire..I am asked to perform
these designs as a part of my job..I am a starter in these and when
ever I have any doubt I would post here..I started as a DB2 UDB DBA and
now juggling between Erwin/UDB/Documentation/Testing..

And I havent found any relevant material which would help..Also suggest
some online course/books (If u have time) so that I can LEARN Database
designing..Coz I want to concentrate on DBA as well as modeling
activities..

thanks a lot...

You need to understand that there are many courses (classroom and online)
and many books that discuss the topic of database design in some fashion. No
one can possibly be aware of all of them or even a large percentage of them.
And even if I was familiar with all of the courses and books, I couldn't
recommend the best one for YOU because I don't know you or how you learn
best. Everyone has their own best way of learning and what works well for me
might not work very well at all for you - and vice versa.

Despite that, I'm going to suggest an example of a classroom course that
looks like it might be good, an online course that will give you at least
some of the basics, and some information in the Information Center about
where you can find some information close at hand. I have reservations about
each of these sources but it's the best I can do for you in the limited time
I have to devote to your question.

First, a classroom course. Since I am in Canada, I went to the IBM website
for Canada and tracked down a database design course. Here is the URL:
http://www-304.ibm.com/jct03001c/ser...seCode=BA161CE

I have not taken this course and I have not seen the course materials but
that description of the course sounds like exactly the things I think you
need to do your job well. I would strongly recommend a course that covers
these topics as the most efficient way of you getting up to speed quickly.
It sounds to me as if you will learn at least the principles of everything
you need to know about database design in just two days. If the course
includes exercises where you do real database designs, it would be even
better. I don't feel very confident in what I've learned until I have
practiced it under the eye of someone, like the instructor, who knows what
they are doing. On the other hand, you may be confident after hearing the
concepts explained, even if you don't get to practice in class.

Now, the remarks at the bottom of the page say that this is a
"Canadian-owned offering". I assume this means it was developed by IBM in
Canada and that it may not be available in other countries - I thik most
courses developed by IBM are available in all the other countries where IBM
operates - but they give you a phone number to call if you're interested.
So, if you call that number, you may find some way of getting that course in
your country. IBM is doing a lot with distance-learning and web-based
learning these days so it may be possible to take this course online somehow
or via a CD. Or maybe your employer will let you go to Canada to take this
course.

Second, an online course. There are many online courses on the subject of
database design. I spent most of an afternoon a couple of years ago looking
at the first several that turned up in a Google search but never found one I
really liked. I did another Google search just now on "Database design" and,
after rejecting the first few, found this link, which isn't too bad, at
least from my point of view. I have no idea whether it will suit you:
http://databases.about.com/od/specif...malization.htm

This site has a number of short articles on database topics. The "Database
Normalization Basics" article seems like a good place to start. You will
need to read several of the "Related Articles" as well, such as
"Introducti ng Keys", "First Normal Form", "Second Normal Form", and "Third
Normal Form". In fact, pretty much all of the articles in the "Database
Fundamentals" section of the index on the left of the page would be worth
your time to read. This site is not particularly well-organized and you will
have to hop around a bit to find all of these articles but if you keep your
eye on the index and the "Related Articles", you should eventually find all
of the articles on database design.

Third, don't forget the DB2 Information Center. If you launch the DB2
Information Center, then click on the "Designing" link in the main index and
expand the "Database Systems" entry by clicking on the + sign, you will see
sections for "Database system overview", "Logical database design", and
"Physical database design". If you read through the articles in those
sections, particularly logical database design, you will get an overview of
what you need to know. I find the presentation of the key ideas in these
topics quite rushed and I'm not sure I would learn them very well if they
were my first and only exposure to this information. But it's a place to
start or maybe it's a place to review the essentials once you've learned the
material elsewhere.

Lastly, "Google is your friend". If you do Google searches on terms like
"database design", "normalization" , "logical database design", "Entity
Relationship Diagram" and other terms that you will see in the other sources
I've given you, you should find lots of useful information. Some of it will
be poorly explained but you can probably skip over those and concentrate on
the ones that communicate clearly TO YOU.

You really need to get a clear understanding of database design if you are
going to have any real chance of building good databases. I urge you to do
as much as you can to learn database design as a very high priority; I think
it will be fundamental to doing the rest of your job well.

--
Rhino

[rAinDeEr]

Hi Rhino,

thanks a lot for taking efforts to find the information. I found the
link informative. It starts from the Basics and you get to know about
all important concepts. The IBM training offered is of class room type
and I dont find myself going in for the training.
I checked it out in my Country but they dont offer it here.

The DB2 information Centre is also great. But the thing is that, there
is not much illustrations. I need to know how Business is converted
into a model. What all are the things we need to concentrate on when we
get the requirement.

By sheer luck, i came upon a great book, Beginnig Relational Data
Modeling by Sharon Allen and Evan Terry . They have a step by step
approach to solve the popular solitaire game. As a start think that is
enough, have to dig in deep now but.

The appraoch in the book is great but at some point of time, it falls
short of expectation (Chap 8). How they realise the model is some thing
confusing.I think i need to have a few more reading to understand it
entirely.

Thanks for sharing u r thoughts...

regards
RaInDEer

[Mark A]

"rAinDeEr" <ta**********@g mail.com> wrote in message
news:11******** **************@ y43g2000cwc.goo glegroups.com.. .

Hi Rhino,

thanks a lot for taking efforts to find the information. I found the
link informative. It starts from the Basics and you get to know about
all important concepts. The IBM training offered is of class room type
and I dont find myself going in for the training.
I checked it out in my Country but they dont offer it here.

The DB2 information Centre is also great. But the thing is that, there
is not much illustrations. I need to know how Business is converted
into a model. What all are the things we need to concentrate on when we
get the requirement.

By sheer luck, i came upon a great book, Beginnig Relational Data
Modeling by Sharon Allen and Evan Terry . They have a step by step
approach to solve the popular solitaire game. As a start think that is
enough, have to dig in deep now but.

The appraoch in the book is great but at some point of time, it falls
short of expectation (Chap 8). How they realise the model is some thing
confusing.I think i need to have a few more reading to understand it
entirely.

Thanks for sharing u r thoughts...

regards
RaInDEer

A database product like DB2 does not come with instructions on how to create
a logical data model for a business process, and only provides scant
information on how to create a good physical database design (DB2 schema).

For the same reason, the lumber, sheet rock, concrete, and roof suppliers do
not provide instructions on how to design a home that meets all of your
functional requirements. For that you need an architect, which is a
completely different activity than database administration.

It is possible for one person to be both a good data architect and a good
database administrator, but being an data architect is not something that
can be easily and quickly learned in the same way you can master the syntax
to use the CREATE TABLE statement properly. Frankly, only a minority of
DBA's are good at either logical data modeling or physical database design,
even if they are very good at database administration.

If you want to learn how to design databases for a business process, I would
suggest at a minimum that you obtain training (or read books) on the subject
of data modeling and normalization. Don't expect someone to provide you with
a "cook book" approach with precise rules to follow, since data modeling
requires a fair amount of intuitive and innate ability that is difficult to
teach, and not everyone can learn how to do it well.

[--CELKO--]

You might want to consider actually using SQL the way you are supposed
to use. Let's get back to the basics of an RDBMS. Rows are not
records; fields are not columns; tables are not files; a column has one
and only one meaning and one and only one data type. You want to use
COBOL or some other language with a variant record type. This is not an
RDBMS at all!

[Rhino]

"rAinDeEr" <ta**********@g mail.com> wrote in message
news:11******** **************@ y43g2000cwc.goo glegroups.com.. .

Hi Rhino,

thanks a lot for taking efforts to find the information. I found the
link informative. It starts from the Basics and you get to know about
all important concepts. The IBM training offered is of class room type
and I dont find myself going in for the training.
I checked it out in my Country but they dont offer it here.

The DB2 information Centre is also great. But the thing is that, there
is not much illustrations. I need to know how Business is converted
into a model. What all are the things we need to concentrate on when we
get the requirement.

By sheer luck, i came upon a great book, Beginnig Relational Data
Modeling by Sharon Allen and Evan Terry . They have a step by step
approach to solve the popular solitaire game. As a start think that is
enough, have to dig in deep now but.

The appraoch in the book is great but at some point of time, it falls
short of expectation (Chap 8). How they realise the model is some thing
confusing.I think i need to have a few more reading to understand it
entirely.

Thanks for sharing u r thoughts...

You're welcome.

I think you understand now that learning how to do database design is not
something you can master in a few minutes or with a couple of newsgroup
posts. You either need to take a good course in it, which would be the
fastest way to learn it, or you will have to slog through books and
tutorials, which may take weeks or months, depending on how much time you
can spend on it.

I feel sure that there are other courses on database design available and
some of them may be offered in your country. You need to ask around and find
out the names of some good training companies in your country and then call
them to see if they have database design courses. I'm sure they'll tell you
if they do and they may point you to someone else in your area who can help
if they can't. Personally, I think a course is the best way to learn
something like this quickly. You won't be a seasoned veteran after only two
or three days in a classroom but you'll have a solid foundation and will be
in a very good position for doing database design on real projects. You
could also try posting on this or other DB2 forums and newsgroups, specify
your country or region, and ask if anyone can recommend a good database
design course for you in your area.

If none of that works, find a good computer bookstore - perhaps at a
university that teaches computer courses? - and spend a few hours there
looking at database design books. Buy the one that explains things best to
you.

And don't be afraid to Google to see what other tutorials, courses, or books
are available on the subject.

--
Rhino