DB Design Issue?!

Hi to ya all!!!

This is my first post to this forum and Im a total newbie to PostgreSQL and DBs in general ...

Im trying to create a DB for my work wich will handle some stuff that we sell ... the problem Im having is that we have both internal and exeternal customers and I dont know how to reflect this in the DB design. For the internal customers we got a couple of master departments and some department under that.

For the external customers I just want to reference to the companies name.

But in the end I would like to have one big Customer table that I can refer to when adding a buy.

The columns that are relevant for both the custom categories are the following:

user_ID
username
password
first_name
last_name
e-mail
phone
mobile

The columns that defer for the two customer categories are the following

master_dep |____ These are for the internal customers
child_dep | A user can be memeber of say social/family

Note that both columns are are alway relevant for the interna customer.

For the external customer only one column identifies it memebership.

company

Well, hope I have made this clear how it works and what I need to get done ... any help is welcome

/Roger

Im putting all my customers into one table ... if both organization and suborganization got values, its an internal user, if only organization got a value, and sub org. is NULL its a external user.

Hope this will work =)

/Roger

Yes, you're going in the right direction. It's always best to generalize and abstract your information. If you separated your customers into two tables, you would be setting yourself up for unforseen problems later.

It sounds like you need to spend a little more time thinking about your table layout, though. It might be better for you to have yet a third table which identifies parent-child relationships between customer records. The basic rule of thumb for normalization is to not have a column in your table which will be unnecessary for a significant number of records.

Also, If your existing data isn't enough to properly separate your internal and external customers, you could just include a simple column called 'cust_type' or something like that. This would allow you to have more customer types in the future. You should include an external validation table called 'cust_types', which lists the possible customer types, and which you can use as the drop-down box for data entry. This keeps you from having incorrect information placed in that column.

If you don't catch my drift, let me know, and I will explain these ideas in more detail.

I will also recommend a couple of good books, like I did in a previous post:

"Database Design for Mere Mortals" (this gives you a great basic approach to databases in general) and "PostgreSQL Developers Handbook".

Thanx for the ideas ... I will consider this. As Im new to this their is a lot to know =)

/Roger