For those new to message boards please try to follow a few simple rules when posting your question.
Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
Keep the subject line brief, but descriptive. eg "File Serialization problem"
Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
No advertising or soliciting.
We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
The term "corruption" applies to the data that is stored by the database server and has nothing to do with valid/possible structuring of tables. Which your other two points would seem to suggest.
Preventing inconsistent data, orphans, etc is not something that anyone is going to cover in an online post. Buy yourself a database book or several (beginning and advanced) and read them before you start designing anything.
I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?
But typically an identity column. The table would have something like the following which is for SQL Server. Other databases all have something similar. Mongodb uses a 'object id' (typically.)
something_id INT IDENTITY(1,1) PRIMARY KEY,
That is how you create the column. But populating it in the other tables is more complicated.
1. You must first insert into the main table to create the id.
2. You must then get that id. That almost always involves using a specific form of the insert (first part) which returns the id at the same time as the insert occurs.
3. You then use that return value to create (insert) into the other tables. Obviously you will need to add a column to hold that new value.
The only possible answer to that question is "it depends". There is no single "best" option; that's why there are different options available. Each option has both benefits and drawbacks, depending on your specific requirements.
For example, if you use SQL Authentication, then you have to store the SQL credentials somewhere on each client machine that needs to access the database. That runs the risk that a technically-minded user could find the credentials and connect directly to your database, bypassing the restrictions implemented in your code. You would have to deliberately design your database to restrict what the SQL user could do in order to mitigate this.
On the other hand, if you're using a web application / api to access your database, then that will typically run as a highly restricted local user on the web server. Setting that up to use Windows authentication is more effort than using SQL authentication. And since all requests would effectively be running as the same local user, you wouldn't be able to use the authenticated user to restrict access to the data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
There is no such thing as "the best option". There is only what is the most appropriate given the requirements for the application, the application type, and the environment it's running in, which we know nothing about.
There are far more options than just the two you listed.
I am going to guess you have an application. Users (plural) use the application not the database.
The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit.
The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.
I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.
I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.
I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1
But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.
Any help will be tremendously appreciated. Thank you.
The difficult we do right away...
...the impossible takes slightly longer.
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?