I'm a database noob...

I have data as follows:

program1
 - company1
   - rate (1.125), cost
   - rate (1.250), cost
   - rate (1.375), cost
 - company2
   - rate (1.125), cost
   - rate (1.250), cost
   - rate (1.375), cost
   - rate (1.500), cost
 - company3
   - rate (1.000), cost
   - rate (1.125), cost

program2
 - company2
   - ...
 - company3
   - ...

program3
 - company1
   - ...
 - company3
   - ...
.
.
.

5/7/2007 10:14:35 AM

programs table:

ID (auto-number), PROGRAM NAME

companys table:

ID (auto-number), COMPANY NAME

rates table:

ID (auto-number), RATE, COST


programs link table:

PROGRAM ID, COMPANY ID

rates link table:

COMPANY ID, RATE ID



And done.

You'll DEFINITELY want to develop a GUI frontend for this, because it will be a nightmare trying to keep up with the link tables maually. But with a pretty simple gui, this will allow you to update everything through a few multi-select boxes.

And it should make updating rates go a bajillion times faster, if, like your example, many of the rates are the same for different companys in different plans.

5/7/2007 10:39:35 AM

if 3 companies have the rate of 1.125, they could each have a different cost with that rate.

5/7/2007 11:06:59 AM

programs table:

ID (auto-number), PROGRAM NAME

companys table:

ID (auto-number), COMPANY NAME

rates table:

ID (auto-number), RATE, COST, Program Link ID


programs link table:

ID (auto-number), PROGRAM ID, COMPANY ID



Much Easier ... multiple rates per program per company, and rates/cost are independant of other companies.

You could even simplify it more and remove the programs link table, and just add the Program ID and Company ID to the "rate" table


[Edited on May 7, 2007 at 12:05 PM. Reason : .]

5/7/2007 12:02:51 PM

Quote :

"if 3 companies have the rate of 1.125, they could each have a different cost with that rate."

I don't see why that would be a problem with Noen's design.

To robster:

Quote :

"rates table: ID (auto-number), RATE, COST, Program Link ID"

I could see doing Program ID, RATE, COST; there's no need for the ID

Quote :

"programs link table: ID (auto-number), PROGRAM ID, COMPANY ID"

Again, this ID is unnecessary.

5/7/2007 12:21:27 PM

when using mysqladmin how do i tell it to link id's? (i.e. rate id actually being a company id link)

also, i'm a little confused on how to actually add the rates for a program/company and make the rates only apply to that exactly.

[Edited on May 7, 2007 at 1:37 PM. Reason : ]

5/7/2007 1:34:54 PM

the table doesn't actually contain explicit references to each other. Like in mysqladmin, you don't add a field that says "link to ID of another table". you have to manage all that yourself in your SQL queries by doing a JOIN.
In your links tables, to make it easy, make a field that has the same ID as the primary key of another table. So then in your query you'll do something like
SELECT * FROM companys_table JOIN programs_table ON companies.company_id=programs.company_id

5/7/2007 1:44:15 PM

when i go through each day and add the new rates, will i have to delete the current rates and then re-add them to avoid having like 100000 rate id's after a while?

5/7/2007 2:01:12 PM

This is why I said:

"You'll DEFINITELY want to develop a GUI frontend for this"

There's no way in hell you can manage a 3rd normal form RDB in the code itself.

It's super easy to manage if you build the composition logic for it (aka a GUI frontend)

Quote :

"Much Easier ... multiple rates per program per company, and rates/cost are independant of other companies."

Robster, the reason I used two link tables was to limit the redundancy of the rates.

And the rates are tied to the company and program, so actually neither of ours would work properly.

You'd actually either need a rates link table like:

COMPANY ID, RATE ID, PROGRAM ID

or a rates table with:

ID (auto-number), RATE, COST, Program Link ID, Company ID

I'd prefer the link table because it reduces redundancy and allows for rate grouping and easier analysis down the line. But it's really a personal preference at this level.

Quote :

"when i go through each day and add the new rates, will i have to delete the current rates and then re-add them to avoid having like 100000 rate id's after a while?"

Depends on your SQL server. With MySQL, auto-increment just keeps going so your ID's will eventually get huge.

But the other thing you can do with frontend logic is to make your own auto-increment to keep the ID's compact.

[Edited on May 7, 2007 at 2:09 PM. Reason : .]

5/7/2007 2:07:46 PM

the whole point of me doing this is to NOT have to have a front end and save time with automation. i'll have all the data in arrays in the program and then add that to mysql... basically destroying ALL rates currently there and re-creating new ones... 99.5% of the time leaving the program and companies in tact.



the reason i want to use a db is b/c this process does intensive disk reads for large excel files, so i'd rather read all this 1 time in a mysql db so php can use that to read everything in.

[Edited on May 7, 2007 at 2:53 PM. Reason : ]

5/7/2007 2:41:04 PM

The only real reason for an ID field is if you want a quick and easy way to reference that record in a table. This way you don't have to compare multiple fields. Each record has it's own unique ID. That said, in this example, you don't need it because other things easily act as unique id's.

5/7/2007 3:35:58 PM

^ Thats true. I kept the IDs in there because it makes it easier to reference in my mind.

I would rather it have its own integer ID that alone can be used to identify the row, rather than having to use a combination of many columns.... Maybe its just personal preference.

^^^ and Noen, I do think mine would still work, as the unique identifier would be a number, so you could have many rates with teh same company/program ids.

[Edited on May 7, 2007 at 5:15 PM. Reason : .]

5/7/2007 5:12:09 PM

so does my rate table actually have:

id (optional i'm sure)
rate
cost
company_id
program_id

then each time i add a rate i find and insert the company/program id? or is that just ONE way to do it?


also should i make rate,company_id,program_id some sort of key?


[Edited on May 7, 2007 at 5:28 PM. Reason : ]

5/7/2007 5:24:07 PM

^^The ID for the rates table is pretty superfluous when you use a reference program and company ID. You can duplicate check with a join on all three items (rate,company,program). The combination of the three will always be unique. So an ID won't really tell you anything more about the data.

I think the issue here is that bous wants a 1st or 2nd normal form database design that's human editable. But the data really calls for a 3rd NF design, which you need some kind of supporting logic to handle with any kind of ease.

You NEED to either write an import script to process the new rates each day or make a frontend for the DB. If you write an import script, then you should be able to handle adding/removing companies and programs just from the SQL Admin. Just have to make sure when removing either that you do a delete on the ID in the link tables.

That would be the quickest and dirtiest way to work it.

Import script would wipe ALL the rates, then ADD all the new rates doing lookups for the company and program to get the correct ID's. If you use a link table, this can be a secondary action (build the rates table, then build the links).

The difference could be pretty big depending on how many total rates (and duplicate rates) there are. If we are talking several thousand total rates, with a high degree of duplicity, it will be MUCH faster to use link tables for DB reads.

5/7/2007 7:52:59 PM

I always use an ID number unique to the rows in that table (usually an auto-number) field.

Pointing out to other tables using thier ID number is expected, but even in your link tables I would have an ID number unique to that row.

To me, the slight cost to the database is outweighed by the potential benefits.

5/7/2007 8:09:20 PM

i already built the backend that adds rates to an array and outputs... so now i just have to add them to the database, which is cake... just gotta decide on the final design.

i'd say... 2000 rates with costs total in the db at a time. i'd say about 40 unique rates out of all 2000.

5/7/2007 11:16:30 PM

so can one company have 2 rates within the same program that are the same??

If so, you need the unique id for the rates table, otherwise, you will not. I would put it there though if it were me running the database... it WILL make it easier to change rates and things later on if you decide to make it more robust, and the extra column is not going to effect performance in your case.

5/8/2007 11:27:18 AM

always will have only unique rates per company per program

i've got it up and running great now. thanks for the help guys.

backend adds all rates to the database. frontend does all the calculations based on those rates (based on input and guidelines).

5/8/2007 1:48:45 PM

never mind

[Edited on May 8, 2007 at 7:08 PM. Reason : ]

5/8/2007 7:03:02 PM

Quote :

"Pointing out to other tables using thier ID number is expected, but even in your link tables I would have an ID number unique to that row. "

What possible use could that have? Having an ID in a link table is redundancy to the extreme.

5/8/2007 7:15:23 PM