How many tables is too many? Start new file?

[geraldh]

Is it best to try and incorporate ALL related data of a business into one file, or should different aspects of your business be divided into different files?

I am looking at helping someone set up a database for an events management business.

I am trying to map the following tables: clients, correspondences, event, goods-services, EventOrderLine.

But is it too ambitious to try and link this with expenses, employee payroll, payments, etc.

Even though some of the data is common between these tables? Just trying to avoid redundancies...

I assume there is some way to share data between files? But is this recommended?

Any advice would be greatly appreciated.

[geraldh]

I've done some more research, and I see it is recommended that one does not try and do a comprehensive database all in one file, and that tables can be linked (easily?) across files. I think this approach will give me greater security over who accesses what.

If you have anything to add from your real-world experience please let me know!

Thanks,

[Lee Smith]

Is the purpose of you question a matter of user access and/or security? How many tables are you planning to use?

Lee

[LaRetta]

Hi yyztoronto,

The base structure is the most important and I always recommend that folks new to relational theory take the time to be sure their base structure is right before they begin building their scripts and user interface. With correct base, all things are possible ... with improper structure, you will hit road-blocks at every turn and you eventually (might) have to remove and replace pieces of your work. Good on you for recognizing the importance of this beginning piece.

I've done some more research, and I see it is recommended that one does not try and do a comprehensive database all in one file, and that tables can be linked (easily?) across files. I think this approach will give me greater security over who accesses what.

I am not sure where you got that information or whether it was mostly a misinterpretation but having multiple tables in a single file is much easier and just as secure. I would recommend at most two files (separation model - one file for data and one file for UI with all scripting and layouts handled in the UI).

I am trying to map the following tables: clients, correspondences, event, goods-services, EventOrderLine.

But is it too ambitious to try and link this with expenses, employee payroll, payments, etc.

Even though some of the data is common between these tables? Just trying to avoid redundancies...

You avoid redundancy by grouping 'same' items into a table and every table should have a primary key (auto-enter serial or UUID) and you place that key in the other tables to relate them. This can all be handled in one file even if the tables happen to reside in different files.

You can always put together the base structure yourself and then post the file for input (this is what I would suggest). Once you have created the skeleton (entity relationships) with the basic fields, the flow of data will begin to show itself. If you start out properly, you can always easily adjust and add additional support relationships. Even if you decide to design anchor/buoy style, creating a basic entity diagram is usually important in getting relational clarity.

[geraldh]

LaRetta,

Thank you for your answer. It was reassuring.

I wish to make it all in one database, and security shouldn't be a problem since only the people who need it, can access it.

I am building slowing and methodically, the base structure, and testing each relationship before I add another table or table occurence.

May I ask, however, what is the merit of dividing your database into the two files (data and UI?)

Thank you again, and perhaps I will post my progress.

[LaRetta]

May I ask, however, what is the merit of dividing your database into the two files (data and UI?)

http://fmforums.com/...ion#entry384360

I gave specific reasons in post 12 why *I* prefer it. YMMV. This thread and many others, provides depth of reasons for both sides. But it seems that every time I create a single-file solution, I end up wishing I had it split (and I usually end up splitting them). And I have never had a solution split and wish I had one solution (except when I need to make a change in security). However, most security issues are small changes, duplicating existing privilege sets etc. and the benefits of separation far out-weight the disadvantages (in my opinion).