Desktop Database App
-
Hello, I am creating a desktop database application with C# and MS SQL SERVER 2008 EXPRESS, and I have a few Q's about it. Let's suppose we have the table customers, with structure like this: Customers: |ID|Nmae |SecondName | Adres |ShipID|Promoted | ------------------------------------------------------ |1 |John |axxx | ytutyt | 5 | false | |2 |Adam |bxxx | qwefsd 18 | 23 | false | |3 |Eddie |cxxx | fsdfssf 7 | 28 | true | |4 |Eddie |dxxx | fsadsdf 42| 14 | true | |5 |Mark |exxx | hdgdssf 2 | 33 | true | |6 |Mathew|fxxx | xcvxzx 75 | 45 | false | I am new in the Database programming, but knowing the goal of my application, I suppose in the future I will experience at least 2 kinds of problems: 1) I guess every month I will have even 100 000 new records but there are necessary only for two months. After that time I would like to move the unncessessarty records somewhere else than in the customers table in order to avoid performance loss, but I'd like to have access to them on demand. Whats the best way to do it? To create other table with schema like Customers, and every two months move data from Customers to that new table, so that customers will always contain data maximally 2 months old? 2) I would like a new logical table PromotedCustomers containing every record from Customers that has "Promoted" value set to true. What is essential here, is that I would like searching in this new table faster than searching in Cusotmers table. For example, supposing I have Customers table like above: SELECT Name FROM Customers WHERE Promoted = true AND Name = Eddie returns 2 records, but has to search 6 rewcords (whole Customers table) If I had PromotedCustomers table the way I want, I could write: SELECT Name FROM PromotedCustomers WHERE Name = Eddie It also returns 2 records, query is a little bit shorter, but whats most important for me: ONLY 3 RECORDS HAS TO BE SEARCHED, and performance is important in my app. Is it possible to create such a table? Thanx for any help :)
-
Hello, I am creating a desktop database application with C# and MS SQL SERVER 2008 EXPRESS, and I have a few Q's about it. Let's suppose we have the table customers, with structure like this: Customers: |ID|Nmae |SecondName | Adres |ShipID|Promoted | ------------------------------------------------------ |1 |John |axxx | ytutyt | 5 | false | |2 |Adam |bxxx | qwefsd 18 | 23 | false | |3 |Eddie |cxxx | fsdfssf 7 | 28 | true | |4 |Eddie |dxxx | fsadsdf 42| 14 | true | |5 |Mark |exxx | hdgdssf 2 | 33 | true | |6 |Mathew|fxxx | xcvxzx 75 | 45 | false | I am new in the Database programming, but knowing the goal of my application, I suppose in the future I will experience at least 2 kinds of problems: 1) I guess every month I will have even 100 000 new records but there are necessary only for two months. After that time I would like to move the unncessessarty records somewhere else than in the customers table in order to avoid performance loss, but I'd like to have access to them on demand. Whats the best way to do it? To create other table with schema like Customers, and every two months move data from Customers to that new table, so that customers will always contain data maximally 2 months old? 2) I would like a new logical table PromotedCustomers containing every record from Customers that has "Promoted" value set to true. What is essential here, is that I would like searching in this new table faster than searching in Cusotmers table. For example, supposing I have Customers table like above: SELECT Name FROM Customers WHERE Promoted = true AND Name = Eddie returns 2 records, but has to search 6 rewcords (whole Customers table) If I had PromotedCustomers table the way I want, I could write: SELECT Name FROM PromotedCustomers WHERE Name = Eddie It also returns 2 records, query is a little bit shorter, but whats most important for me: ONLY 3 RECORDS HAS TO BE SEARCHED, and performance is important in my app. Is it possible to create such a table? Thanx for any help :)
I think your data design may be flawed (I don't know your business so I cannot be more definite), the ShipID on your customer table makes me suspicious that you are creating a new customer per order. You should have at least 3 tables to support an order processing system. Customer, Order and OrderLines. 100k customers per month also makes me suspicious of your structure. 100k records per month is not a large volume (for SQL Server, I don't use express) and I would look to archiving annually rather than monthly. Then I'd use an OLAP cube to retain reporting infrastructure. Promoted is a flag/attribute of the Customer table, not a separate table. Remember every field does not need to be filled in the table. I would make the promoted field a date field giving you the option of making time based queries.
Never underestimate the power of human stupidity RAH