database design

[davebat]

I am trying to design a set of tables but am having some trouble. The end result is I am hoping to store the data for seven reports, each report will contain various measures and each measure will have a different value for each of the seperate vendors (currently 5). so far I have somethign like this in mind.



But the problem is when there are more vendors, i dont want to have to re-design the data table but cannot think how to do this. My end result would be a table that looked like data with the vendors value in different columns but I want to create from an sql pull of the other tables.

[szlamany]

DataID table cannot have those 5 vendor columns.

It needs to be further normalized - each vendor needs a different row in this table. So you can have a one vendor, two vendors or ten vendors for a MeasureId in this table.

Several ways to do that - we would add a VendorId column right after the MeasureId column and make a compound primary key of these two columns.

[davebat]

thanks for the quick reply.

I designed it like that originally so the data table looked like this

MeasureID
VendorID
Year
Month
Value

but eventualy i will need a table that is created dynamically that looks like this:

Measure
Data Source
Target
Then a column for each vendors value
vendor1val
vendor2vale etc

would i do this through sql afterwards?

[szlamany]

Why do you need that as a table? How can you predetermine the max number of vendors so that those columns can be built?

What is your backend database?

Do you simply need that to be returned in a SELECT statement?

[davebat]

yes, sorry thats what i meant. I want to return this through a select by joining the tables and i will know the max number of vendors as this is contained in the vendors table.

Sorry for not being clear

[szlamany]

What is your backend database?

.......

[davebat]

backend database is run from sql server 2000

[szlamany]

Well - taking 3 rows of vendors and getting them to be 3 columns in a SELECT is never an easy task.

Most would say that it's a client-side task - do it in code.

But since you are using SQL 2000 you could write a STORED PROCEDURE to loop through the vendors and tack them onto separate columns. You could use a cursor for this (usually not recommended - but probably your only option).

[davebat]

thanks a lot for your advice. I'll give it a go, there will never be massive amounts of data so a cursor may be viable.