Customer Table Design

[Sinfathisar]

Hi, its been a about a year since I've worked on database design, but my employer has a database that was created by a previous employee around 5 years ago and it needs some redesign. I've searched to see if I can find anyone with a similar problem, but haven't had any luck so I thought I would send a shout out here for some help.

My company is production based. Everything revolves around each specific order - but the customer (person we ship the order to) is often different from the entity/person whom we bill the order to (i.e. we ship orders to college students but their university department pays for it).

We currently have one customers table with ship tos and bill tos both in there (numerical distinct ID's are preceeded with a C for ship to and a B for bill to) and duplicate information must be entered when the ship to person is also the bill to person.

I can't seem to find any advice that will help me decide if I should keep the same design, make one ship to table and one bill to table with complete identification information (i.e. address, phone #, etc..), create one table with only customer ID and two fields with check boxes (one for ship to and one for bill to) and two linked tables for customer ID and address information (one for ship to and one for bill to), add similar check boxes to those mentioned above but instead put them in a single customer table, or add separate bill to/ship to address fields to a single customer table (will require many empty fields).

Do any of these ideas seem like the way I should go? Or are there any additional ideas out there? Thanks in advance for any pointers....

 

[vbaInet]

Just had a quick glance. What I would do is instead of prefixing the IDs with "B" or "C", in your list of orders you simply have the CustomerID in two fields, one field for Bill To and the other for Ship To. From those fields you can point to the Primary Address of the Bill To or Ship To Customer.

I would imagine that a University can also be a Bill To and Ship To customer at the same time. If this is the case then you can create another field in the Customers table to identify whether that record relates to a Student or University.