On 3/8/2011 12:30 PM, Darren New wrote:
> Tom Austin wrote:
>> 1. Is the practice of creating separate databases for each 'job'
>> considered good or bad? Is this answer different based on using MS
>> Access .mdb files vs using a MySQL Database engine?
>
> As others have pointed out, if you're ever going to want to do any sort
> of cross-job statistics (like knowing if the same person worked on two
> particular jobs, etc) then you need to have one database.
>
> The only time I've seen multiple databases with identical schemas used
> that was *not* a f'up, it was because it was a software-as-a-service and
> each customer got their own database and essentially managed it
> themselves. I.e., if you can't imagine installing each database at a
> separate company, you probably want one database.
>
>

Yes, I can think of a few instances of where it could be very useful.  I 
am trying to feel out how usual the practice may be. Thanks for the info.


>
> Each table that does not necessarily have any references to its rows is
> representing a separate real-world entity. For example, you might have a
> customer, a sale, etc. Subsidiary tables such as individual lines of a
> sale, might be separate in tables, but they won't represent a separate
> entity. (You can usually tell because the row in the subsidiary table
> will have an arbitrary primary key (if the PK isn't just a combination
> of FKs) *and* a FK that's exactly the PK of what it's a subsidiary table
> to.) That, IME, is approximately the right way to think about it and
> break it down. Build the DB as tables of real-world objects and events,
> then hook them together appropriately. You'll almost automatically be
> "normalized enough". The time you get denormalized is when you start
> thinking about how you're going to be processing the data before you
> think about the actual data, and you start doing joins in your head and
> encoding them into the schema.
>

Interesting way of looking at building a schema - just break it down to 
objects.  I think this fits our data very well.  I think we were doing 
this anyway, but not on purpose - more by accident.

> Another good way of working it is to see if there's any combination of
> columns that can be a primary key that isn't just some arbitrary number.
> I built an entire database designed to track people's financial accounts
> (on behalf of a bunch of *our* customers) for a stored-value web site
> that didn't have a single auto-increment field in it. (Granted, our
> customer supplied the identifier for *their* customer, so without that I
> would have needed one auto-increment field.) But everything else was
> keyed off that. The credit card number was the primary key for the
> credit card. The sale's primary key was the credit card number and the
> UTC timestamp of when we made the sale. The end-user's PK was our
> customer's PK concatenated with the identifier we had assigned to our
> customer. In other work, places were keyed by the three-letter code of
> the airport closest to the place (i.e., that's how we identified
> cities), radio stations were identified by their location and frequency,
> etc. It's not always possible to find such a key, but to the extent you
> can, it tells you a whole bunch about the meaning of the record. And if
> you use that and find it too slow, *then* it's easy to change it to a
> simple integer identifier.
>

Sounds like you had fun.  I like how you had to get creative on how to 
set primary keys - and using a pre-established system to boot (the 
airport codes) - genius.


> As for text, I've found that a very good trick for readability is that a
> CHAR(4) will index just as fast as an INT. So if you want the database
> to be both fast and readable with codes, you use codes like 'SALE',
> 'RFND', 'VOID', 'CUST', 'MNGR', 'EMPL', etc. Then, if you want, you can
> have another table mapping these codes into a varchar(250) or something
> to put in drop-down lists.
>

this might be worth considering, but I don't know how easy it will be to 
implement and keep straight.  Seems like the a user created key like 
this could lead to duplicates - but if it is a key it will prevent 
duplicates anyway - just prompt for another key.

> If you're talking about text or integer for other things, ask if there's
> any possibility the text will ever change. Is it the name of a company
> or person? A phone number or zip code? If so, it needs to be in a table
> and not part of any key.
>

Most of the data in question is a simple map from an integer key to a 
piece of text.  I do have some queries on the text with joins to pull 
data from other tables.

The text is not designed to be changed.

The alternative that we have is to flatten the relationship and put the 
text directly in the table instead of having a key reference.

ie
go from

tablePole		tableOwners
Pole   OwnerID		ID	Name


to

tablePole
Pole	OwnerName



Any thoughts on this?


>> We are developing a new database system to take care of some severe
>> shortcomings of the old system.
>
> What are the shortcomings?
>

Let's just say is was a single table database with about 250 columns - 
limited only by the fact that Excel at one time only allowed about 250 
columns.

Putting data in and getting data out was very difficult and required the 
sacrifice of too many brain cells.


>> Our data has a lot of common 'choose from' items - such as the owner
>> of the pole.
>> The data consists of pole specific information that doesn't change
>> (owner, height, etc) and information that will likely change (the
>> height of cables on the pole).
>
> These last two should probably be in separate tables that you join at
> runtime. (Especially if you're using MySql, where differnet "engines"
> give you different capabilities.)
>

That's what we are trying to figure out - how to make it make sense to 
have them in separate tables.



FYI, my statements and questions may seem vague or uninformative.  I am 
trying to get input without having my bias guide it.


Thanks

Post a reply to this message