Sales & Purchase Order Database

[MattBaldry]

Just after a little advice in case anyone has ever built something like the subject line.

I have done a few different ones and have always had a separate table for customers and suppliers. I have started to notice that some people have a customer who is also a supplier. Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both?

Or would it be better practice to continue with the trusted method I already base this on?

~Matt

 

[CJ_London]

Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both?

possibly, really depends on how your db is intended to work. I've used both methods in the past. A single table takes more managing and won't really show much benefit is space saving unless you have many customer/suppliers. Also have to consider data security - do you have users who works with customers and other users who work with suppliers?

If you stick with separate tables, you could include a flag field to provide the supplier PK in the customer table (or visa versa), doesn't require a flag in both tables.

 

[vba_php]

Would there be any benefit in having a single table and a field that allows to be either customer, supplier or both

if you're using a server oriented database application system, I believe some of them have capabilities like this, but I would doubt that anyone would recommend it. you can obviously do this in access to by modifying it to an extreme via various UI and VBA methods, but it's probably not worth it. IMO, i would just stick to what you've been doing.