Dynamic creation of tables for expanding company

[Andreas T.]

Background:

My client runs a growing company with currently 3 departments. All departments will be using the solution. There will be more departments in the future.

The client wants all the departments to share customer, product and stock data, but he wants them to have separate quote, invoicing and cash register access. He especially wants to ensure that serial numbers are unique and consecutive for each department, which leeds me to having them in separate tables.

As it is, I have built some of the solution around 3 separate tables and sets of layouts for the various sections that have variable access and have set up a user authentication scheme to direct users to their appropriate layouts.

This is all well and good, but as the client's company is growing fast with more departments on the horizon, I am looking for a better way.

I am hoping to achieve a level of abstraction so that I can dynamically create tables as needed and have "global" layouts and multitable scripts and so on.

This is also because the clients is looking to repurpose the solution to franchise holders that may also have separate departments and in addition will be remote, so I can't get to them easily.

I am looking for input on how to best go about this, especially if I am way off base and there is a better way to accomplish my client's needs.

Sorry for the long winded post, but I felt the need to explain the concept.

Cheers,

Andreas

[Ender]

I'd strongly recommend using the same set of tables for all departments. It's much easier to maintain things when you only have to remember to do them once.

It's a little more work up front to make it enforce security rules to only allow people into their own department. But this is really just a small extension to the kind of security rules that should be there anyway--allowing users only to see their own stuff.

The consecutive numbering by department requirement is a little puzzling. Why is this important? What happens when the serial numbers are the same for different departments? Or is there some requirement to avoid this? Anyway, while there are methods to so this, it can be problematic.

[T-Square]

I support Ender on this--you can create a single set of tables for the different entities (People, Products, Invoices, InvoiceLineItems, etc.) and then use login-based filters to limit people to the records in their own department.

WRT the department-based sequential numbers, you might want to have two separate fields in your system--one field an auto-enter serial number that is used in all database structural relationships (which your bosses don't ever have to see or worry about), and the second an auto-calculated (non-primary key), department-based counter that looks nice to the bosses (but doesn't do anything in the database).

This auto-calc field can make use of a self-join relationship to find the highest last number for the given department and bump up from there.

But of course, since this is not a functional part of the database per se, it might be a lot of work, and I wouldn't do it unless there were a compelling reason to do so--like someone will throw you in jail if you don't have them.

HTH,

David

[Andreas T.]

Thank you both for your valuable input.

I would like to clarify what I mean with consecutive serial numbers.

For instance, in the invoice table, as each department has separate accounting, the invoice numbers need to be consecutive for each department, that is, no holes in the number series. Holes would appear in a regular table with a auto-enter serial number for the invoice number, if I put all departments in the same table without further logic.

Now, the solution to this would be to script the next serial number, and I am going to look into this, working on the suggestion from T-Square.

I already have all the relevant data for the selected department in globals in a prefs file, so this would reduce the layout count as well, after a little reorganisation.

Again, thanks a lot for your input, it looks to have saved me a lot of time in the future.

Cheers,

Andreas