Database design

[davebat]

Is it possible to design a database that will hold imformation from excel, but it spossible that the structure of the spreadhseet will change?

[jp140768]

It depends. Are you expecting the structure of the database to change to match the changes of the Excel spread sheet, or will what ever loads the database from the spreadsheet change, to keep the database structure in tact.

I wouldn't advise constantly changing the structure of a database - it can lead to problems with existing data, and also the application / queries that are built over it.

HTH

[penagate]

Yup.

Keep your DB structure rigid, and use some kind of mapping tool to import data from structurally volatile spreadsheets.

Is that what you're after?

[mendhak]

It's possible if you store the 'column names that could possibly change' in a table which holds such column names with an id which can map to values you hold in another such table.

[Al42]

Mendhak, we do thousands of spreadsheet imports a week, using exactly what Penagate suggests - using a mapping file as an intermediary. The program reads the mapping file, which tells it what to do with every column in the spreadsheet. (We've run this on spreadsheets with a few hundred columns and 65k rows with no problems.)

@Penagate - I never noticed the 'Moderator' tag before. Are congratulations in order?

[davebat]

thansk for the replies, this is what i was thinking. The only problem is if I mapped to a data column how would this one column hold different types of data? ie dates, integers etc

[randem]

Why do you need to store this info in the database and how do you want to retrieve it?