|
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue)
Example:
100,Color,Black
100,KosherCertified,False
100,InsuranceValue,123.99
A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products.
Just an idea. ![Java | [Coffee]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/coffee.gif)
|
|
|
|
|
And AttributeType, so you can cast the attribute to the right type. And then normalize the Attribute and AttributeType into its own table.
Just my $0.02
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Fairly basic question really, if each of my Tables has a Primary Key of UNIQUEIDENTIFIER, when I create a Foreign Key to point at the PK of another Table, what Data Type should it be ?
|
|
|
|
|
Also uniqueidentifier. Types of PK and FK needs to match.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Really ? I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field ... I have a lot to learn here  !!!
|
|
|
|
|
Hi,
As I understand it, A Unique Identifier is only created if you also set it as Primary Key.
Regards 
Bram van Kampen
|
|
|
|
|
Gary Heath wrote: I (obviously wrongly !) assumed that a UniqueIdentifier value would be generated anew when a record was created, rather than used from another field
They're not generated by default; it's just a datatype to hold GUID's. For the Primary Key to generate one, it'll have to have a default - usually coming from the NewId function.
The PK is the unique value that identifies your record. An FK only references it; we copy the unique value of the PK that we want to link, and keep that information in the FK.
Person
PersonId
Name
OtherStuff
User
UserId
PersonId
Person1: 145, Joe, Stuff
User1: 132, 145 -- referencing a user by using it's PK
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I think it should be on same data type
|
|
|
|
|
hi,
i am using vs.net 2010 to make a web service project with linq to connect to a ms sql server.
however i notice that although i added the annotation for the classes classes that need to be persisted.
it is still very tedious to make changes to the class it self as i need to manually go into the database to make the table changes.
can anyone advice me if i should continuing using linq or is there something better out there? which is as simple to use as linq but can automatically change the ms sql table to reflect the annotation i used in my classes
|
|
|
|
|
|
neodeaths wrote: or is there something better out there
Incremental changes handled over time to a database schema with the only record being C# code annotations would be a nightmare for a non-trivial business system.
Of course in terms of that it makes me wonder how you are tracking your changes to the database now given that you are doing it in "the database".
|
|
|
|
|
Hi,
I am using C#.Net-VS2012 and SQL2008R2.
My Computer Network name is b-pc, The Connection is Softguard.
I have the following code snippet:-
SqlConnection myConnection = new SqlConnection("server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"connection timeout=5");
myConnection.Open();
It excepts with the following reason:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
.....And Much More!!
It seems to look for some form of authorisation.No username or password was provided (nor asked for) when the connection was created new in DbExplorer. Widows Authorisation was used at the time.
What more must I do to connect to the server.
Kind Regards,
Bram van Kampen
|
|
|
|
|
Either use integrated security (the preferred way) or specify a user. Bookmark this[^] site, add below part to the connectionstring;
Trusted_Connection=True;
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks,
That worked! Unfortunately, the whole proces turns out to be One Step at the Time!
It connected to the SQL Database, to arrive at the Next hurdle:
Exception:
-Quote: System.Data.SqlClient.SqlException (0x80131904): Cannot open database "SgTextiles" requested by the login. The login failed.
Login failed for user 'b-PC\Bram'.
Now, this very database exists in Ms SQL Management Studio under the Path:
-Quote: b-pc\SOFTGUARD\Databases\SgTextiles
It is definitely populated with tables with content. The correct spelling and case has been meticulously checked. The Tables were constructed and populated with the SQL management studio, and, also saved. In a further check, (done while writing this, I performed a 'Save All' in the studio, and closed it down. (this went without any complaint from the Studio about Unsaved Files etc) When re-starting the studio, The database had the 'SgTextiles', and all its components in the tree display. When Clicked upon, there was a Dlg stating that 'SgTextiles' is un-available, and the branch cleared. SgTextiles is still unavailable, and is displayed as if totally empty.
Any idea what other "magical incantations" are required to get to the next step.
To anticipate a following issue, I named the first two tables "Users" and "Roles". They are shown in the studio as "dbo.Users", resp "dbo.Names". Is this the correct way of addressing these tables in a queery string, or should I leave out the 'dbo' part.
Thanks for your much appreciated help sofar.
Regards,
N.B.(Modification)
A thought just entered my mind. I am running all this software under a Standard Win7 User Account.
Is there a problem with permissions? I have both Amin and User Access to this machine.
In retrospect, I noted a few times SQL Studio purporting to do thing without complaint, and then not doing them, without stating so. I had already a few detours via the 'Admin', trying to set HKLM Reg Keys by hand.
Thanks
Bram van Kampen
|
|
|
|
|
If you can't get it with a trusted connection, try creating a named user in SQLServer and give that user access to the database. Then changed your connection to use the username and password you assign.
|
|
|
|
|
Bram van Kampen wrote: A thought just entered my mind. I am running all this software under a Standard
Win7 User Account.
The user that creates the connection, regardless of how the user is specified, must have appropriate rights in the database to access the resources that are needed.
|
|
|
|
|
Already two good answers in my absence, and I'd like to expand on them.
Bram van Kampen wrote: The Tables were constructed and populated with the SQL management studio, and, also saved.
Which credentials did the Management Studio use to connect to the database? 
Bram van Kampen wrote: They are shown in the studio as "dbo.Users", resp "dbo.Names". Is this the correct way of addressing these tables in a queery string, or should I leave out the 'dbo' part.
The dbo is the schema-name of the "database owner". In the ideal case, one would create a new schema and assign the appropriate rights to that.
Whether you (want to) use the prefix in the SQL statements depends on how portable (or strict) you want your queries to work. Without specifying the schema, it's easier to reuse the query under a different schema. OTOH, I often even prefix the databasename and the server to identify a table;
SELECT <columnlist>
FROM server.databasename.schemaname.tablename
WHERE 1=1
This way I'm pretty sure that I'm selecting exactly what has been specified, regardless of which is the "current" database, or even the current machine, and copy/paste errors show up as a parser-error before the command is executed. Omit the server/schema, and you can guess what happens when you do a "DELETE FROM Users" when you've selected the wrong server by accident.
Bram van Kampen wrote: I have both Amin and User Access to this machine.
Yes, but that's local to your machine - the database-server has it's own security. There's two modes; first there's Windows Authentication, which takes the user that's currently logged into Windows to authenticate (and is DBO when creating a table). One doesn't need a password in that case, since you already provided a username/password when logging into Windows. That's the preferred way. Alternative, there's "mixed mode", where SQL accepts a username/password combination (with "sa" usually being the name of the admin account).
First, you'll need to know how the server is configured. That's the same as when using the Management Studio, and as quoted above, you've already used it to login when creating the tables.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
Eddy Vluggen wrote: The dbo is the schema-name of the "database owner". In the ideal case, one would
create a new schema and assign the appropriate rights to that.
Whether you (want to) use the prefix in the SQL statements depends on how
portable (or strict) you want your queries to work. Without specifying the
schema, it's easier to reuse the query under a different schema. OTOH, I often
even prefix the databasename and the server to identify a table;
It seems that I am getting caught upin a lot of esoterics. 
I have no idea what a 'scheme'is, or, where I would need it. Maybe an explanation of what I'm trying to do is required.
I am trying to create an SQL Database for small laundrettes, drycleaners, garment alteration places and shoe menders. None of the individual databases are intended for exposure to the internet. However, the software will have to operate at up to 32 terminals on a LAN, all accessing the same database (and potentially concurrently the same record.)
The software uses it's own authentication methods (that's the part I'm trying to write now),and the user terminal would run in a 'generic Windows User Mode'. The round trip to change 'Window User' is too time consuming in practice, combined with the fact that in principle, no other tasks are ran on the machines. (The Machines act as the end user's Production terminals or cash registers)
We have a system in place (written in MFC) that runs on XP and does all this, but uses a proprietary DB system, implemented by file sharing.
The DB lay-out would be fixed for a particular version for the software, and there should be a way to 'rol out' the version to our customers. DB lay-outs could change between SW versions, but, it is accepted that no transactions take place during the upgrade process.
The Database should resist any queeries other than queeries issued by the licenced software we issue. This is a core security principle. It should be difficult (if not impossible) to raise queeries on the database using third party software.
The Cornerstone Issue for the system is Consistency and immediate inconsistency rejection. This means that a transaction must fail if the state of the component parameters of the transaction when the transaction started differ from same when the transaction is committed.
Maybe I'm on the wrong track by using SQL in the first place.
I look forward to your observations,
Bram van Kampen
|
|
|
|
|
A multi-user database-server, with facilities like atomic transactions; yes, that's a good place for Sql Server.
Bram van Kampen wrote: The Database should resist any queeries other than queeries issued by the licenced software we issue. This is a core security principle. It should be difficult (if not impossible) to raise queeries on the database using third party software.
Sql Server doesn't care.
I don't know of any database that checks the users' license; as long as he/she is authenticated, the data is theirs. Yes, SQL Server offers a lot of security options, but I don't think there's a way to throw the local admin out, or prevent someone from running an SQL-profiler. You "could" encrypt everything in there, using the license-key as the encryption-key.
Bram van Kampen wrote: Maybe I'm on the wrong track by using SQL in the first place.
It's still possible to write binary files, but then you'd have to worry about consistency a bit more.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy,
That was most helpfull.
I have opened the DB Management suite in Admin mode, and, Yes, My database is there, in all it's humble glory, complete with data. I just cannot get access to it from my User account. (Strange, I Created it from my User Account.) I thought, I soldier on for now by running as Administrator. Fat Chance, as Administrator, I cannot even see my own User Folders (I thought that Administrators could access ALL Folders.)
How do I make this DB accessible from User Mode.
Eddy Vluggen wrote: Sql Server doesn't care.
I don't know of any database that checks
the users' license; as long as he/she is authenticated, the data is theirs. Yes,
SQL Server offers a lot of security options, but I don't think there's a way to
throw the local admin out, or prevent someone from running an SQL-profiler. You
"could" encrypt everything in there, using the license-key as the
encryption-key.
What if I use SQL Authentication with a unique Name and Password for each user, and permanently block Windows Authentication. Would that have a chance of working?
The software can only be procured from a Softguard Dealership,and is subject to an annual licence + fee. Producing individual credentials for each individual user would not pose a problem.
Eddy Vluggen wrote: It's still possible to write binary files, but then you'd have to worry about
consistency a bit more.
Been there, Done that, I have the teeshirt. 'a bit more' is not the term, 90% of the DB Code ends up worrying about it one way or the other. It is manageable on a system with one machine. Imagine the scene with say 6 computers, say 4 Shopfloor terminals, where one has a list of orders waiting for completion, and where staff enters priceing up and packaging details,and two sales terminals, where raw laundry is taken in and processed laundry is handed back to customers. Many terminals typically chasing the same limited subset of the same database. No One terminal can 'Hog' any component or entry of the DB. Doing so would stop others from working. A User display of say an Order Content is obtained, to give the user an environment to work in. By the time the user adds his contribution, the contents may have been changed by another user. We have over the years developed algorithms to manage these situations.
Another design consideration was that a terminal could simply 'Die' halfway trough a transaction. even that should not leave locked items, or partially completed transactions.
I wonder, looking to the future, what provisions there are in SQL to ease this task.
Enough about musing about the future, the task at hand.
Access to the DB from aUser Account, as mentioned above.
The studio generates a panoply of items. I understand that the studio tries to do all things for all people. Do I need them all, can I cull some.
You mentioned that 'dbo' means Database Owner. Each of our end users can only have One and One Only copy of the Database on their system. Who qualifies as 'dbo'. It suggests that several Windows Users can each havetheir 'Own'copy of the database. If that's the case, it has to be stopped. It would allow staff to set up their own shop within a shop, which is clearly unacceptable.
Regards and Thanks
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: I have opened the DB Management suite in Admin mode, and, Yes, My database is there, in all it's humble glory, complete with data. I just cannot get access to it from my User account. (Strange, I Created it from my User Account.)
That's strange indeed - I'd go for a reinstall of SQL Server using the default options.
Bram van Kampen wrote: I thought, I soldier on for now by running as Administrator. Fat Chance, as Administrator, I cannot even see my own User Folders (I thought that Administrators could access ALL Folders.)
The Windows-administrator isn't automatically the administrator of the database-server. That's a good thing, it means that a local admin at your client isn't automatically the admin of the database-server.
Bram van Kampen wrote: What if I use SQL Authentication with a unique Name and Password for each user, and permanently block Windows Authentication.
That's technically not possible; imagine the server running for say, a nuclear plant. Next, we shoot the only three people who have a password: that'd mean that the data would be permanently locked.
If security states that no-one is allowed in the database, then you only option is to not have a database on their network; but that's a bit too extreme and radical for such an application, and hosting your data in a cloud would mean some lag.
Bram van Kampen wrote: Been there, Done that, I have the teeshirt.
Hence the suggestion for SQL Server, to quote;
“Some mainframe users still wonder if SQL Server is reliable enough for them. We're a nuclear power plant—how much more reliable do they need it to be?”
Janice Hoerber, IT Supervisor-Applications, AmerenUE Callaway nuclear power plant The downside is that it requires research into securing the database. You'd need these;
Bram van Kampen wrote: You mentioned that 'dbo' means Database Owner. Each of our end users can only have One and One Only copy of the Database on their system. Who qualifies as 'dbo'. It suggests that several Windows Users can each havetheir 'Own'copy of the database. If that's the case, it has to be stopped. It would allow staff to set up their own shop within a shop, which is clearly unacceptable.
"Oops[^]" - can be changed[^].
Bram van Kampen wrote: Enough about musing about the future, the task at hand.
How about a bit of both worlds? A single dedicated database-server, some cheap machine that's totally under your control, gifted to the client, running Linux. Have a MySQL (or SQLite) database on there, password-protect it, password protect the root, and do all communication over TCP/IP. Log every connection, every request.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi, Eddy,
A lot more food for thought!
Eddy Vluggen wrote: How about a bit of both worlds? A single dedicated database-server, some cheap
machine that's totally under your control, gifted to the client, running Linux.
Have a MySQL (or SQLite) database on there, password-protect it, password
protect the root, and do all communication over TCP/IP. Log every connection,
every request.
I do not know which jurisdiction you live in. Jurisdiction has always an impact on software of the kind I write. It always has to take into consideration the legal system of the country in which we license it for use.
Whereas we have Licence control over the software, we want to steer clear of providing hardware as far as possible, and even less of claiming ownership of hardware on customers sites. Softguard could easily become liable for loss of business and consequential losses if their 'owned' a crucial hardware component (in the case you suggest, the server) Fails in any way or another. This has to do with legal issues under the 'Sales of Goods Act' in the UK, an equivalent acts acros the EU. We provide Cash Drawers and Till Roll Printers for sale. These are easily guaranteed, relatively simple, and replaced under guarantee at nominal costs when failing.
Our customers process financial data on the system. Our fiduciary duty of care includes protecting all data to the extent that it can only be released with the consent of the owner. If the owner dies, for whatever reason, and does not leave any access details to recover the information, then so be it; The data must die with the owner, whatever the consequences.
Your example of a Nuclear Power Station is entirely wrong in the context. Our software is subject to the data protection act. If our software fails, it will never cause a nuclear explosion.
'MySql' I found very hard to come to terms with from scratch. The support is rather thin. I have over the last 6 years made at least three attempts to get it to go on Win XP.
Learning to write in using SQL first, may be the better way for getting round to learning my way around. I take it that the difference between SQL and MySql statements can be largely handeled mechanically. (That was the purpose of 'Standard Queery Language' in the 1990's.
At the same time, your suggestion makes sense! I can always provide a machine with a modified Linux/MySql version which excludes the windows logon to the database, even when copied to another regular machine.
All these idea's are great, Now I have to start to commit to practice.
Regards
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: Softguard could easily become liable for loss of business and consequential
losses if their 'owned' a crucial hardware component (in the case you suggest,
the server) Fails in any way or another. This has to do with legal issues under
the 'Sales of Goods Act' in the UK, an equivalent acts acros the EU. We provide
Cash Drawers and Till Roll Printers for sale. These are easily guaranteed,
relatively simple, and replaced under guarantee at nominal costs when failing.
A database-server could be as simple as MySQL on a raspberry Pi (albeit no match for an x86). Give them two Pi's (or equivalents), each with a unique IP. Have the client-application write to both db-servers, and "read" from the one that's available
..or "sell" those to the client as part of the solution, so that it's no longer "your" apparatus. If you're not allowed to sell hardware, sell them two virtual machines with Linux. Might indeed be wise to have someone check the license before embarking on such a design.
Bram van Kampen wrote: Your example of a Nuclear Power Station is entirely wrong in the context.
I'd rather be on the safe side and not find out.
Bram van Kampen wrote: That was the purpose of 'Standard Queery Language' in the 1990's.
Still is. I try and keep stuff in SQL-92, old standard but seems to be well understood by most database-drivers.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
Well, all good food for thought.
There are now two issues.
The first is an apparent bug in VS2012 which replicates a known but unresolved bug in VS2010. An internal error is caused, aparently by the Build Software being unable to create a pipe to the IDE. This stops the build from starting. (according to smart people on stackoverflow). It crept in end May, as an occasional annoyance, and has grown now to a major issue. However, there is not much I can do about it, MS has to resolve this.
The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works. The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date.
Bram van Kampen
|
|
|
|
|
Bram van Kampen wrote: The second is that I need a proof of concept, which has no security implications. If I can make my SQL DB open to all callers (i.e. No Security checks whatsoever) I can show my supporters something that works.
How about switching temporarily to MS Access?
Bram van Kampen wrote: The security aspect is a matter of DB configuration and Connect String. That can be sorted independently at a later date.
Nothing is as permanent as temporary code. If it works without "fixing", then there's a good chance it won't be fixed.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
