Each customer will have their own database.  My plan is to create one database that contains 50-100 tables of static data that might change every 3-5 years.  These static tables will be used by every customer.   I thought keeping this static data separate might make the administration simpler but maybe not.  What do you guys think, is this a good idea or just a waste of time?

Also, the static data is in tab delimited files with names like "EXTENT OF FIRE INVOLVEMENT ON ARRIVAL" so I name the new EDB tables the same name.  I then import the data into the new table.  Are these long names that include spaces going to cause me problems down the rode?

Thanks,
TD

On 10/30/2014 12:07 PM, TD wrote:
> Each customer will have their own database.  My plan is to create one database that contains 50-100 tables of static data that might change every 3-5 years.  These static tables will be used by every customer.   I thought keeping this static data separate might make the administration simpler but maybe not.  What do you guys think, is this a good idea or just a waste of time?

In general separate DB sounds like a good idea.

However, what are you going to use these tables for?

Do you need to at any point join the data from static tables with the
customer data for example ?


> Also, the static data is in tab delimited files with names like "EXTENT OF FIRE INVOLVEMENT ON ARRIVAL" so I name the new EDB tables the same name.  I then import the data into the new table.  Are these long names that include spaces going to cause me problems down the rode?

Not spaces in db name though i personally avoid this. EDB does have some
limits on thing (like identifier length of 40) so at the very least make
sure everything fits. Here are the limits :

http://www.elevatesoft.com/manual?action=topics&id=edb2sql&section=appendix_system_cap

Raul

Raul wrote:

On 10/30/2014 12:07 PM, TD wrote:
> Each customer will have their own database.  My plan is to create one database that contains 50-100 tables of static data that might change every 3-5 years.  These static tables will be used by every customer.   I thought keeping this static data separate might make the administration simpler but maybe not.  What do you guys think, is this a good idea or just a waste of time?

In general separate DB sounds like a good idea.

However, what are you going to use these tables for?

Do you need to at any point join the data from static tables with the
customer data for example ?


> Also, the static data is in tab delimited files with names like "EXTENT OF FIRE INVOLVEMENT ON ARRIVAL" so I name the new EDB tables the same name.  I then import the data into the new table.  Are these long names that include spaces going to cause me problems down the rode?

Not spaces in db name though i personally avoid this. EDB does have some
limits on thing (like identifier length of 40) so at the very least make
sure everything fits. Here are the limits :

http://www.elevatesoft.com/manual?action=topics&id=edb2sql§ion=appendix_system_cap

Raul

Thanks Raul.  The static data will be used to fill combo-boxes on forms.  The value showing in the combo-box is saved to a field in a table in the customer's database.  If you are referring to creating joins as in left join, right join, then no there shouldn't ever be a need to do that.

I am thinking I will replace the spaces in the table names with an underscore just to be safe.

Thanks again,
TD

TD

<general rant>

I hate really long names cos I'm lazy and don't like typing them, also tehre's a possibility that I'll screw up and get a character or two wrong.

I also like to have table components have the same name as the tablename so spaces are a big no no

</general rant>

> Each customer will have their own database. My plan is to create one database that contains 50-100 tables of static data that might change every 3-5 years. These static tables will be used by every customer. I thought keeping this static data separate might make the administration simpler but maybe not. What do you guys think, is this a good idea or just a waste of time?

>Thanks Raul. The static data will be used to fill combo-boxes on forms. The value showing in the combo-box is saved to a field in a table in the customer's database. If you are referring to creating joins as in left join, right join, then no there shouldn't ever be a need to do that.


With the usage you're describing I think 50 - 100 separate tables is a bad idea. I'll let you decide if my concept is better or not.

First point is that to populate the various comboboxes you have to read the data in. ElevateDB has the nice LIST command in its SQL and that's pretty good but you're still going to have to read through every table to populate the combo boxes.

What I do is have a single table (in my case its multi use holding lots of configuration / setting data). All you need is a table with two columns. Mine is a bit more complex since I allow the users to edit the bumph

CREATE TABLE "Config"
(
"_ID" VARCHAR(45) COLLATE "ANSI_CI",
"_Type" VARCHAR(1) COLLATE "ANSI_CI",
"_UserSettable" BOOLEAN DEFAULT FALSE,
"_ParamData" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Description" VARCHAR(65) COLLATE "ANSI_CI",
"_Group" VARCHAR(15) COLLATE "ANSI_CI",
"_SubGroup" VARCHAR(15) COLLATE "ANSI_CI",
"_Editor" VARCHAR(15) COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)


_ID could be 'EXTENT OF FIRE INVOLVEMENT ON ARRIVAL' with _ParamData holding a list of the various possibilities

Populating the combobox would be

ComboBox1.Items.Text := GetCfgStr('EXTENT OF FIRE INVOLVEMENT ON ARRIVAL');

where

function GetCfgStr(const cID: string; const Default: string = ''): string;
begin
 if Config.FindKey([cID]) then Result := Config_ParamData.AsString else Result := '';
if Result = '' then Result := Default;
end;

Roy Lambert