Help With Some Database Logic

[mdumka]

Hello,

I am trying to construct a Database for a client.

He has 3 tables, Customers, Clients, Suppliers.

Primary Keys:
Customers (customerNumber, AutoNumber)
Orders (orderNumber, AutoNumber)
Suppliers (supplierNumber, AutoNumber)

Relationships
Customers to Orders (via customerNumber)
Suppliers to Orders (via supplierNumber)

Now with his current database in the Customer Table he has Account Numbers in field Names.

For Example one fieldname is IBM_account_num, another is MICROSOFT_account_num. We want to design it so those fields dont have to be hard coded.

If he were to open an account with DELL, the only way to update the DB is add another field. There must be another way.

I am thinking adding a 4th table and calling it Accounts and relating the Customer and Suppliers tables through it simular to the way Orders was.

Primary Keys:
Accounts (accountId, AutoNumber)

Relationships
Customers to Accounts (via customerNumber)
Suppliers to Accounts (via supplierNumber)
Orders to Accounts (via orderNumber)

Is this kind of logic ok, or am I heading in the wrong direction?

Thanks

[Imminent]

You've got the right idea.

A customer can have many accounts.
An account belongs to only one customer.

= One-To-Many relationship.

This means accounts should be its own table.

[mdumka]

Awsome, I was just having a heck of a time getting my head around this.

I can work with DBs but have never had to build one before.

Thanks