My team is building a self service portal for users who want to purchase the data from us. Registered users will log into our portal and make the purchase depending on the criteria they specify. For example, I am a registered users who wants to know who is growing what crops or if they have any livestock etc. The table which contains all the details about any crops and livestock has about 5 millions records. The server we have has enough resources. 128GB of RAM, 64 cores (don't know about storage).
I am pretty sure that we won't run into any performance related issues but I still want to make sure we build this portal in a way where we don't run into issues later. So the question is that I am not sure which is going to be optimal for performance if we let users put in the number where they specify potatoes = 200 or potatoes > 200 or is it better to have them select from one of the options from the drop down menu where they will have an option to choose from these values (101 - 250, 251 - 500,501 - 1000.1000+)
Table structure:
CREATE TABLE [Crops](
[FarmID] [int] NOT NULL,
[Potatoes] [int] NULL,
[PotatoesBand] [varchar](10) NOT NULL,
[TotalLivestock] [int] NULL,
[TotalLivestockBand] [varchar](10) NOT NULL,
) ON [PRIMARY]
How about:
CREATE TABLE [Crops](
[FarmID] [int] NOT NULL,
CropID INT NOT NULL,
Quantity INT NOT NULL
Units VARCHAR(10) NOT NULL
CONSTRAINT pkCrops PRIMARY KEY (FarmID, CropID) );
But without more detail, it's hard to know for sure what the proper design should be.