Database Theory Questions 2

[Scott24x7]

Okay, another installment of VFP7 questions... This time, the topic is databases. I understand the concept of the "Database", vs. "Tables", and how the DBC works. However, I have a few loose ends that I'm not clear on, and would like some advice on how to best handle these situatoins.
I have an application that I convereted from 2.6 to 7, but I want to take things to the next phase. This includes beginning to "Remove" the 2.6 nature of the app, and turn it into a true "OOP" product. In the process of that, I want to convert my existing tables, (which are all currently Free Table, and not even "In" my project), to Databases. The things that are not entirely clear to me are, how to decide what tables belong in the DBC. Also, for my application, should I have more than one DBC that is specific to the "Type" of data, as it is related to itself. (For instance, all employee information, such as the Employee table, Address Table, Jobs Table, and Supervisors Table, seem to logically "Stick" together. So, I thought I would create a "Employee" DBC. Then, I have Inventory, Bill Of Materials, Bundles, which seem "Inventory" related, so make an Inventory.DBC. Then I have Customers, Addresses (seperate, for Customers), Invoices, Payments, which I would put in the Customer.DBC. Does this philosophy make sense, or, should they all be put into a single "Application".DBC?
Second question is around "Code" tables. I have a number of standard "Lookup", or "Code" tables for things like, AdressType, City/State/Zip, Job Codes. Should they also be contained in the "DBC" for their respective type, or should they remain as "Free Tables"?
Can someone shed some light on this philosophy for me? I have a firm grasp of relational tables, and have done a lot of work to ensure referential, key, and data integrity in 2.6, that seems to be much easier to do at the "DBC" level. How should I approach this in my migrated application?
I've read a number of things on the subject, but they don't really seem to give me clear deliniations of when I would decide to do these things, or the rational behind the decision making.
For any help, many thanks,
-Scott

 

[ChrisRChamberlain]

Scott

I don't wish to sound negative about your proposal to go 'dbc', but I would suggest you carefully consider what advantages this may bring to your project.

Judging from your description of the way your free tables are organised, the benefits may not necessarily be sufficient to warrant the effort entailed.

and have done a lot of work to ensure referential, key, and data integrity - they would be some of the issues a 'dbc' would address, so you appear to be further down the track than you might think.

FWIW, I would concentrate at this stage on the OOP aspects of VFP 7.0, rather than the 'dbc' aspect.

Chris

 

[fluteplr]

Your making your life much more difficult than it needs to be. Create one DBC, and put all your tables into it. The only exception to this is that I sometimes have a 'config' or 'options' table as a free table if that table might hold startup information such as which DBC to use.

 

[weedz]

What I have done in my CrownBase application (see my signature) is the following:

I have three types of databases:
1. Collections (that is, for every collection the user adds, a different database is created, you could compare this with a company in an accounting application there canb be more than one company)
2. System database (in this database all system files are stored, this is the data that is always overwritten when the installation updates the application, this data is only determined by the programmer). This database contains the translation tables, the fields table, the tables table and the index table (I use table buffering, so no views) which are used to update existing data in the collection DBC's and the SYSDATA DBC.
3. Sysdata database, this is the application wide data database (so in every collection the user can use these data.)

For me this groups my three types of data nicely together so I always no where to find the data.

The system database and the sysdata database are always open.

The collection database is determined by the user since there can be more than one collections. But only one collection can be open at every moment.

Hope this gives you some inside in to how you could use databases.

HTH,
Weedz (Wietze Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

And especially for VFP rookies:

http://fox.wikis.com/wc.dll?Wiki~VFPRookieMistakes~VFP

 

[jhlautomation]

Scott,
you're on the right track, just do not go to far with this DBC feature of Xbase.

If you need to use the relational database feature you'll have to place the tables you wish to relate in the same DBC.
Remember the indexes and I'm assuming you're using .CDX files. When someone turns the power off or pulls the plug
you may need to add Maint. Utilities to fix these things when you're not available.

In general keep all other tables for reports or views FREE to give you the old Fox2.5/2.6 capabilities and also allow the use of SQL to create these FREE tables in any ORDER were needed for different reports only on the Client Station that requested the report.

Regards
Jack

 

[csr]

I would agree with an earlier comment about considering whether or not you really need to enter the arena of the ..... DATABASE (DBC). It is not all roses. You will encounter a number of what I affectionately refer to as PAINS ... Things you could easily accomplish without databases becomes a real challenge.

I would suggest porting things over to VFP and forgetting about the database. Just use free tables ... they will work fine. Then, if you wish to ... begin fooling with a database in a limited manner on something simple to see what things jump up. Trust me, you will be much better off.

Don
dond@csrinc.com

 

[jjgraf]

If you want to go all out switch to the MSDE which is SQL server 2000 with out the graphical tools.

I have found it works faster when you have data shared out across a network

That is a complete change from the Way things work concerning data.
I would suggest that you completely start from scratch if you want to be that crazy

 

[RedLion]

When your really so far, and you’ve got the time to port everything over to the new development world, with OOP, I suggest also going to use databases, you can better monitor Referential Integrity (RI), and you can take advantage of so much futures I really suggest to use them, and about the problems with databases, the only thing that you have to make is a program to maintain your index’s for when your application is not closed well (the power goes down), and that isn’t so difficult. And the question what tables you should place in the database, I would suggest to place all tables that are part of your application / system to place in one database. Tables that originally don’t belong to your application shouldn’t be placed in your database. And some free tables that you will use only for the client for some settings, and aren’t shared should also not be in your database.

Charl

 

[ThomC]

I'll add to the growing sentiment: You probably don't need to put your tables into a DBC.

Pro DBC:
1) Aids referential integrity.
2) Facilitates development of a new database (highly debatable)

Con DBC:
1) Adds complexity.
2) Reduces flexiblity. The headers of the individual tables are "tagged" with their parent DBC container, thereby requiring the existance of the DBC... you can't just copy a DBF to another directory and use it. You have to either make the table a free table or COPY/SQL SELECT the contents to another physical file.
3) IMO, VFP's DBCs are not yet mature. For example, the DBC will retain the index expression of only one TAG of a CDX for a table. If the CDX breaks, you can't use the DBC to rebuild the TAGs. You have to have the index expression information in code, in a separate meta-data table, or in your head.

A company I worked for was porting a major database (it was the core of the company's only product) from 2.6 to VFP 5. It was an extremely aggressive port (we figured out a way to write all of our business code in a business tier and direct the code to either VFP, SQL Server or Oracle... same code was used across all the DBMS') but we rejected the DBC concept because it was too limiting and didn't provide anything we couldn't impliment easier and faster through code and good design.

Regards,
Thom C.