So here's the thing. I am a new coder and I am working on my first big project from scratch. My project is using ASP.NET MVC, Google Maps API, Entity Framework, with SQL Server.
I got a very early protype started on my local machine but I'm starting to run into database design complications. I am already new to coding, but somehow have managed to figure out MVC and get a Web API going. However, I am even less experienced than that with databases and database administration. I know how to add/remove database objects and set up tables and all that jazz; that's not a big deal. But I'm having questions such as:
*What is the best way to associate user accounts in my system with their business' information...
*I want my users to be able to add/remove products to their business' inventory table... What is the best way to set up the database to store each individual product.
*What is a good way to pair the information given to me via business owners with the data I pull from Google API.
*Should I create a new object for each inventory item or just list it in a long text string of all inventory items since no details will be needed?
*When do I have my application create an entire new database versus just add a new table in an existing database? For example, should user accounts and businesses be in two separate databases?
These questions are less coding questions and more database setup/administration questions... Questions I'd love to ask someone who has made a big modern commercial database before that handles accounts and inventory and all that. Obviously, coding questions will come up too but a lot of it is just database design.
So, my real question here is... How do most coders do this? I would iamgine a lot of coders are not DB experts... Should I buy a Database Administration book and read it? Should I consult an expert? Since this will be a data-driven application, database setup is important. Thanks!
PS: Please, don't worry about me being overwhelmed. I mean, if this is something that will seriously take 6 months of dedicated study, it may be better left for me to call up someone else... But even though I just started programming a few months back, I'm actually loving getting into all of this and it is not intimidating at all.
Do NOT get a book on database administration, yet. Get a book on database DEVELOPER, admin and dev are very different roles. There is also as site with sample database, ah found it[^], pick one close to your industry and try and understand why the design was used. He is pretty good and faithful to the rules of DB design.
After you have got a feel for DB design feel free to ask silly, or not so silly question here.
Your first 3 questions are answered by foreign keys
You should create 1 table per CLASS of inventory (if an items attributes are so different to an existing class then create a new class).
Your application should NEVER create a new database except for backup (and this should be an admin job not application).
If your scale is going to so huge (as in multiple of terabytes) that storage is an issue then you could split the data into different databases but I have never had to do that.
Never underestimate the power of human stupidity
If you're working on enterprise or Web Applications, it's pretty much a given that you'll need to understand how databases work. There's not getting away from the fact that you can mess yourself up badly if you don't understand normalization, concurrency, and relationships.
The current bar is SQL, and if you're interested in web apps I would strongly encourage you to familiarize yourself with relational design; your model class designs should be informed by the relational system and, more importantly, normalization.
Once you understand the relational schema, then make your life easier by having a look at document stores like Mongo.
"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli
I mean, if this is something that will seriously take 6 months of dedicated study
Not sure how to address that part. A database admin who comes into a shop with no other admin should have about 5 years of experience doing just that. So 5 years of a 40 hour a week job.
A place that wants to hack it a bit more with small initial aspirations but whose goal is to deliver a product could get by with a developer who has had 2 years of experience working with databases via a programming language (so familiar with the database but primarily programming is C#, Java, etc.)
How do most coders do this?
Do it every day for years.
Since this will be a data-driven application
Very little that isn't but that doesn't insure that you must use a database.
Please, don't worry about me being overwhelmed...I'm actually loving getting into all
So dive in. You can't learn it if you never start.
There are books about programming with databases. You should find one of those. Database design is a different issue just as design itself is a different issue and I have never found a book that really teaches design well (of any sort.) The basics of programming however does lend itself well to books.
If you don't mind reading then one that focuses on database programming and another that attempts to teach database admin will, over time, help. I have never been a DB Admin but the admin books I do have have been helpful.
As a suggestion try to keep your design as simple as possible.
I am trying to pass a varchar string from C# to MySQL stored procedure to be used in WHERE xx IN (param). I tried it in the basic way below but it's not working and giving no result.
can any one help please.
this is my C#:
sql_connection = new MySqlConnection(serverClass.connectionstring("BlueFile", "BlueFile", "Server"));
sql_command = new MySqlCommand("sp_populate_memo_country_companies", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.AddWithValue("param_country", Convert.ToString(cboToCountry.EditValue)).MySqlDbType = MySqlDbType.VarChar;
// Create data adapter object
sql_adapter = new MySqlDataAdapter();
sql_adapter.SelectCommand = sql_command;
// Create a dataset object and fill with data using data adapter's Fill method
data_set = new DataSet();
DataViewManager dataview_manager = new DataViewManager(data_set);
DataView main_dataview = dataview_manager.CreateDataView(data_set.Tables["companies"]);
cboToCompany.Properties.ValueMember = "location_id";
cboToCompany.Properties.DisplayMember = "company_name";
cboToCompany.Properties.DataSource = main_dataview;
DevExpress.XtraEditors.Controls.LookUpColumnInfo("department_name", 50, "department_name"));
cboToCompany.Properties.PopupSizeable = false;
cboToCompany.EditValue = null;
and this is my stored procedure:
SELECT locations.location_id, companies.company_name, locations.location_name, first_payroll, last_payroll
JOIN country ON country.country_code_alpha2 = locations.country_code
JOIN companies ON companies.company_id = locations.company_id
LEFTJOIN payroll ON payroll.location_id = locations.location_id
WHERE locations.country_code IN (param_country) AND payroll_active = TRUE
Excuse my writing of the statement, it's probably my fault it's not formatted correctly, I'm not that familiar with SQL, I'm just trying to determine if there's a security/privacy issue of someone possibly accessing unread PMs of the forum users.
I suppose another way of saying this, is the query a generic query you'd expect in a mybb forum database, or could it possibly be used to list (maybe not the contents but like a directory or list of PMs that they can then pick out which take their interest) unread PMs that could then be read manually?
I'm just trying to determine if there's a security/privacy issue of someone possibly accessing unread PMs of the forum users.
Yes, by looking at the queries. The query itself won't be showing intent. It may be malicious, it may just be a dev that is testing. Problem is that you do not know the origin.
The fact that you are looking at them implies to me that you do not trust the security of the database, to which I'd have to agree. Instead of looking at a logbook who was in your house, one should be checking the lock and which users have keys. If you are confident about the lock and keys, then it makes little sense to go ask the person who entered the house what their intent is.
If the data is saved in a readable format, then yes, anyone with access to the table could read it. It does not matter what data it is - goes for (read) PM's as well as other tables.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
If the query is actually executing in your database, then you urgently need to review your code to fix the vulnerability. SQLi leaves you vulnerable to a lot worse than information disclosure - it can be used to alter any data in your database, and potentially modify your OS, depending on the configuration.
If it's just appearing in your web logs, and not getting through to the database, then you can probably ignore it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It looks like somebody is trying to find and exploit a SQL Injection vulnerability in your site:
I don't see anything in there to escape the current command executed.
You could still be correct, in line with post #2; if the connection-string is exposed, anyone could use that to issue commands. It would be something used to explore the database, which could still be valid use -
More questions; is there a table with that name? Have there been other commands from the same source? Could it have been generated by a tool? (Ever seen what traffic SQLSMS causes?)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
There's likely a table with that name given it's a mybb forum, although I see where you're coming from, was it custom created for that purpose? Why would there be a table specifically for unreadpms for all users? I hadn't thought about that.
That thought worries me. They entrusted the SQL side of things to members that I personally did not know the reputation of, and I'm not sure how trustworthy they were (I personally hold the ethos you should only entrust to those you can trust).
All I know is information appears to leak from PM but only recently did I put two and two together (I'm not an admin on the site so it's not something I'd spotted conventionally).
Someone made vague insinuations another forum was dealing with an SQL injection, and that remark always bothered me. But I didn't want to prejudice the first hand analysis.
I think best course of action is to assume worse case scenario, assume there is an injection attempt and find a way to lock it down. If there isn't, site gets extra security and I just look daft, and if there is, everyone benefits.
So next question of course is how do you deal with a site that has presumably been attacked by an SQL injection?
Every now and then the forum spews an error specifying the query. It's not in a log, the forum owners are friends and I've done a little bit of SQL for another project but my knowledge is minimal, so when I saw a query asking for unread PMs, it made me suspicious.
The query is verbatim from the error, not correctly formatted I suspect.
They would have no reason to run it, and they aren't SQL savvy enough to construct SQL queries of that nature. So whoever is running it is SQL savvy.
Other forums in our localised community have been hacked, with one fairly recently taken down (the admin's login details were stolen), and we often found information mentioned in PM was somehow 'known' publicly (I conducted experiments between my forum and theirs and found any info in PM on their forum leaked).
I didn't want to preload the dice in my favour by mentioning this, I wanted to see if the query could be an innocent outcome without the prejudicial information to hand.
The fact people are verifying it's asking for all users' unread PMs (as opposed to all PMs in general or a specific users' PMs) verifies my suspicions there's been a database breach.
Do you have any recommendations for securing a database against SQL injections (or maybe some way to test to see if it's vulnerable)?
I don't think extra security could hurt here even if I'm wrong.
To secure the site, you need to modify the code. You'll need to find every place where it issues a SQL query, and make sure it's using parameters. If it's using string concatenation, or string interpolation, or any other means of inserting data directly into the query text, then that's a potential vulnerability.
If your database has any stored procedures that build and execute a dynamic query, you'll also need to update those to use parameters. In Microsoft SQL Server, you would use sp_executesql[^]; other databases probably have something similar.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
as the title already say, I'm searching for the right database for my use case which should work with the following data:
- 2 key/value "tables"
One which holds a string as key and a normal number as value, the second table should hold also a string as key and a string as value.
The first table should hold billions of string/number values, the second one should hold millions of string/string values. So there should be stored a huge amount of data.
The only operations I need to do are the following
- constantly add new entries in both tables
- before adding a new entry, check if the key (string) is already stored
- search for entries which share the same key in both tables. In a relational DB I would execute this statement (select * from tab1, tab2, where tab1.key = tab2.key) -> This search should be as fast as possible.
I'm experienced in any kind of relational DB like oracle, mssql, mysql, postgresql....
As key/value database I only used redis so far.
I think my use case is not good for relational databases. Some NoSQL databases meight be a better choice. Redis is not good as it is an in memory DB with data size limits of the physically memory. As I'm using lot of data I need something else.
I think my use case is not good for relational databases
Why you are thinking a relational db is wrong here? For me it looks like a rdb fits very good.
before adding a new entry, check if the key (string) is already stored
For this you can define the key as Primary or at least Unique. So, before insert you do not Need to check whether it is allready in. Simply insert, the db will tell you then, whether it was allready in. The Advantages: No multiply checks for unique values(one from you explicit, one from DB while checking constraints) and last but not least, no race condition.
One Thing more, MS SQL with its "Clusterd Index" fits Performance whise perfect for key/value pair.
There is only one Thing: "billions of string/number values" looks like you can not go for the free Version because of restricted db size.
Thanks for your answer.
I just thought that a key/values store meight be a better solution. They are usually designed to be fast with key/values... But I'm not very experienced with NoSQL DBs, so I meight be wrong with this.
If I use a relational db I know how to use PK. For the first table I still need to read it first, because if the key already exists, I also need to update the value of this entry. For the second table it would work just to let the DB check the constraint if the entry already exists or not.
MS SQL is not my first choice, because I also want it to work on Linux systems as well.
Some points to consider:
- are your keys case-sensitive? In MS SQL, string comparison is normally not case-sensitive, while with postgres or Oracle it is.
- what is the relation ship between the two tables? Do I understand you correctly that there are keys which exist in Table1 only, and other keys which exist in Table2 only?
- do you need some kind of reporting? I.e. how many different keys can be found for a value for keys exiting in both or only one table. Complex aggregation queries work fastest in MS SQL or Oracle, while mysql copes with simple aggregations only (but with two tables, that should still be ok).
- What would you do if a key to be added already exists? Update the record or throw an exception?
Thanks for your reply.
- the keys are all in uppercase in both tables, so a case sensitive match works perfect.
- The first table is the leading data store, the second table is more or less a lookup table. I want to check which entries in the lookup table exists also in the data store table.
- I don't need to search for any values, I need to search only for keys.
- If a key in the first table already exists, I need to update the value (increase the value)
- If a key in the second table already exists, nothing happens, just continue with the next one. But inserting doublicated entries in the secend table are extremly unlikely, so there is no need to check them. It's no problem if for some reason there are really doublicates, to add them twice. But if I use a rdbms and define the key as PK, this check is done automatically.
I will give it a try with a rdbms (not MS SQL because I want it to be available also on Linux systems).
But I'm wondering if a simple NoSQL DB (simple key/value) store meight not be faster in this case. I don't have much experience with NoSQL dbs, but I think they exist for a reason. And I want a really lightning fast solution
But if you think a normal sql db can be as fast (or faster) than I give it a try.
Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components isset on database connection manager.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
1. DelayValidation is set true.
2. Excel Connection String is set as
Error 3 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. ---> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0
but, when i put HDR=YES the error was change. it says neesd new metadata, its seem like i have different format of file. event thought the file was the same.
It is very difficult to guess what is going on without more information about the actual code that causes the exception. Please show the code where the error occurs, indicating the exact line that raises the exception, and the exact content of all variables that are being used.
Hi every body,
I have a very special an uncommon! issue with CDC data migration, this is my problem: we had enabled CDC backup on our DB setting cleanup date for 2 years, so after 2 years we have old CDC data only in DB backups, now our customer needs reports base on CDC data in past 4 years, so the only way we can do that is reinserting old CDC data in corresponding tables by script, we replaced "change_tables" and "lsn_time_mapping" and set the data cleanup date for 10 years. every thing is OK until the cleanup job runs and removes the old data, is some data missed for restoring or is any setting avoid this cleanup?
every suggestion would be appreciated
Last Visit: 31-Dec-99 18:00 Last Update: 1-Oct-16 3:28