Return to Answer

###The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

###The performance

You may be more worried in performance. What is important there, is to index the tables at least on their ID fields (for accelerating the joins). Also index the date in DailySales (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The biggest impact on the performance with MSAccess, is the multi-user access, since every PC will run an MSAccess engine that will have to access the file on its own, whereas on a DBMS you'll have a dedicated server process. However, in your use case, you only have one PC, so this should not be your main concern.

###The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

###Conclusion

If despite your arguments, the owner doesn't want to invest in a DBMS, you can certainly start small with MSAccess. If after the first years, the performance will decrease significantly, despite indexes and other optimizations, then you could switch to a more robust system.

The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

The performance

You may be more worried in performance. What is important there, is to index the tables at least on their ID fields (for accelerating the joins). Also index the date in DailySales (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The biggest impact on the performance with MSAccess, is the multi-user access, since every PC will run an MSAccess engine that will have to access the file on its own, whereas on a DBMS you'll have a dedicated server process. However, in your use case, you only have one PC, so this should not be your main concern.

The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

Conclusion

If despite your arguments, the owner doesn't want to invest in a DBMS, you can certainly start small with MSAccess. If after the first years, the performance will decrease significantly, despite indexes and other optimizations, then you could switch to a more robust system.

The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

The performance

But you may be more worried in performance. What is important there, is to index the tables at least on the ID fields (for accelerating the joins), and on the DailySales date (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The biggest impact on the performance with access, is the multi-user write access, since every PC will have to access the file on its own, whereas on a DBMS you'll have a dedicated server process. However, in your use case, you only have one PC, so this should not be your main concern.

The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

Conclusion

If despite your arguments, the owner doesn't want to invest in a DBMS, you can certainly start small with MSAccess. If after the first years, the performance will decrease significantly, despite indexes and other optimizations, then you could switch to a more robust system.

###The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

###The performance

You may be more worried in performance. What is important there, is to index the tables at least on their ID fields (for accelerating the joins). Also index the date in DailySales (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The biggest impact on the performance with MSAccess, is the multi-user access, since every PC will run an MSAccess engine that will have to access the file on its own, whereas on a DBMS you'll have a dedicated server process. However, in your use case, you only have one PC, so this should not be your main concern.

###The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

###Conclusion

If despite your arguments, the owner doesn't want to invest in a DBMS, you can certainly start small with MSAccess. If after the first years, the performance will decrease significantly, despite indexes and other optimizations, then you could switch to a more robust system.

The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

The performance

But you may be more worried in performance. What is important there, is to index the tables at least on the ID fields (for accelerating the joins), and on the DailySales date (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

The limits

The volumes should not be a problem for a modern database.

MS-Access has a constraint of maximum approximately 2 GB per table. Looking at the type size, it appears that one record of DailySales is currently around 24 bytes. Let's round it to 40. This means that MSAccess would still be able to store 50 millions records, which means 64 years of sales data if your shop makes in average 15.000 lines per week.

A more concrete constraint could be the type of the ID field. If you go for auto-numbering, which is a 4 byte unsigned integer, you'd be limited to 4 million records, a limit which could be reached within 5 years already. A workaround could be to use a composed primary key, with the business year and the autonumber, and reset the autonumber every year.

The performance

But you may be more worried in performance. What is important there, is to index the tables at least on the ID fields (for accelerating the joins), and on the DailySales date (for accelerating sorting).

Just for illustration, indexing allows the database to find any record in 10 years of sales data in less than 15 reads, instead of going through 7 millions records.

The biggest impact on the performance with access, is the multi-user write access, since every PC will have to access the file on its own, whereas on a DBMS you'll have a dedicated server process. However, in your use case, you only have one PC, so this should not be your main concern.

The design

Without knowing the objectives, it's difficult to judge the design. But from what I can see:

• Quantitative sales statistics on products will be easy, assuming that the unit type of products never change.
• Sales figures will be difficult to compute because there's no easy join between ProductPriceHistory where the price is stored and DailySales which holds the quantities to multiply with the unit price. You'd better store a ProductPriceHistoryID in the DailySales.
• I'd even suggest to store the price used in the DailySales, because this could allow to register ad-hoc rebates, in case of customer bargaining or small issues on a specific product box.

Conclusion

If despite your arguments, the owner doesn't want to invest in a DBMS, you can certainly start small with MSAccess. If after the first years, the performance will decrease significantly, despite indexes and other optimizations, then you could switch to a more robust system.