CODEWITHSUNDEEP
databasedatabase-design
wootscootinboogie
wootscootinboogie

Reputation: 8695

Database design for a yearly updated database (once a year)

I have a large database which will only be updated once a year. Every year of data will use the same schema (the data will not be adding any new variables). There's a 'main' table where most of the customer information lives. To keep track of what happens from year to year, is it better design to put a field in the main customer table that says what year it is, or have a 'year' table that relates to the main customer table?

Upvotes: 0

Views: 553

Answers (3)

Matt Harrison
Matt Harrison

Reputation: 358

Usually you would split off your archive data because you are doing OLTP stuff on your current data, because you want to mostly work on current data, and sometimes look at old stuff. But you have very few updates it seems. I guess the main driver is your queries, and what they 'usually' do, and what performance you need to get out of them. Its probably easier for you to have everything in one table - with a year column. But if most of your queries are for the current year, and they are tight on performance you may want to look at splitting the current data out - either using physical tables, or partitioning of the table (depending on the DB some can do this for you, whilst still being a single table)

Upvotes: 0

luv2code
luv2code

Reputation: 1296

I recommend having a year field in the customer table, that way it is all together. You could even use a timestamp to automatically input the date of user sign up.

Upvotes: 2

Collin
Collin

Reputation: 12287

To really answer, we'd need to see your schema, but it is almost never the right choice to make a new table for a new year. You probably want to relate years to customers.

Upvotes: 1

Related Questions