Database Setup and Design

[cvillard]

I have a couple questions I hope someone might be able to answer, or
rather point me in the correct direction.

I am an independant developer and I am working on a small CRM for small
businesses. Nothing fancy, but built with c# and the .NET framework.

In my study of other CRM's like Microsoft, Clarify, and Misc I have
noticed something things that I do not understand.

1. When using a database, are customers and the customers
cases/problems kept in seperate tables?

2. When logging notes and misc, how is text formatted/kept in the
database. I see notes inside cases that are formatted and keep the
formatting after the cases are written the the DB.

Thanks for any info, and I am sure I am asking more then simple
questions.

[cvillard]

Sorry had one other thought. Say if customers are tracking emails or
phone messages, is a seperate table setup for each call/email or would
there be a notes database with a related key #.

[David Portas]

In a relational database a table represents an entity - informally, a set of
things that are alike in the sense that they have a common set of
attributes. So one might expect to see one table for Customers, another
table for Cases, a table for Invoices, etc. Data in tables are related by
keys. The database design will usually be static once it is built - the
design may be changed if the business requirements change but it isn't
necessary or desirable to create new tables at runtime. Database architects
use a set of rules called Normal Forms to help determine how to model
entities in a database.

In a nutshell, those are some general principles. However, my experience is
that commercial software packages built on relational databases sometimes
tend to use those databases in ways that are very non-standard and peculiar
to that application. The usual assumptions don't always apply. I don't know
about any of the packages you mentioned though so I could be wrong.

For storing formatted text there are various options. As RTF or HTML in a
text column for example. Or as a formatted document stored as a binary
database object.

Hope this helps.

--
David Portas
SQL Server MVP
--

[Gregory Dean]

On 2/8/05 6:32 PM, in article
11**********************@o13g2000cwo.googlegroups. com, "cvillard"
<cv******@gmail.com> wrote:

I have a couple questions I hope someone might be able to answer, or
rather point me in the correct direction.

I am an independant developer and I am working on a small CRM for small
businesses. Nothing fancy, but built with c# and the .NET framework.

In my study of other CRM's like Microsoft, Clarify, and Misc I have
noticed something things that I do not understand.

1. When using a database, are customers and the customers
cases/problems kept in seperate tables?

2. When logging notes and misc, how is text formatted/kept in the
database. I see notes inside cases that are formatted and keep the
formatting after the cases are written the the DB.

Thanks for any info, and I am sure I am asking more then simple
questions.

We have a several applications where we store the data with HTML tags so it
displays formatted on the web site.

-Greg

[cvillard]

Thank you both for the information, this is really helpful and I think
I have some good information and ideas to start with. Much Appreciated.

Thanks again,
Chuck