|
Hello experts
I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain).
Why?
The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type….
Ok, long speech. What I’m really like asking for Best Praxis is:
I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default.
In case this “user-id” allows alphanumeric, what is better/best Praxis/usual:
a.) Make Ids case sensitive ?
b.) Make Ids case insensitive?
Thank you in advance
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Since you can do "like" and "sounds like" searches in SQL Server, you need to review your "requirements" more.
For the record, the recommended approach for "entity keys" are "nonsense numbers"; i.e. identities / generated id's. And they're central to Entity Framework's "tracking" ability.
It's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email; in which "case", you can case it the way you want when comparing.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Thank you, I need to think more about
Quote: 's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email.
Thanks again
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
I find that the more systems I'm integrating with, the more I rely on creating "API's" where the object "getters" do some of the translating on the fly for a better or cleaner "view" within the context of the application. Molding reality.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Store all in upper-case and go for insensitive. Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
For primary keys, I still recommend normalization. Your auto-generated identity is of little use to the user (and should not be visible).
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Quote: Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
That is exactly the Point for which I'm looking for. Completely agree with you. Thank you for confirming this.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
You're welcome 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I want to ask how Facebook database works like? For example if I consider only the FB posts that users put on this timeline, how that post table would work?
Some statistics.
1. There would be more than 1 million posts every second.
2. Other users may be commenting on that posts too.
Questions.
1. Is there only 1 post table or multiple posts table that contains user post based on their geographic location. This may help in reducing the load from a single post table.
2. Is Fb database has some different approach then the databases of normal (low traffic) website?
3. Every database whether Oracle or SQL server has a limit in terms of number of concurrent connection, no of inserts queries performing per second and so on. So how FB is doing this?
Hope my questions make sense. Anyone who might be in FB database team might help me in my questions?
thanks
|
|
|
|
|
|
i need help with my new database program that has to do with Promotion of Staff to a new level. The database have three (3) tables: namely
1. <b>Promoted List table</b>, which have the following columns; (Id No., File No., Name(Staff name), Date of Birth, Date of First Appointment, Date of Present Appointment, New Level, Old Level, State Office location, Branch Office, State of origin and Year of Promotion).
2. <b>Ommitted List table</b>, which have the same columns with the "Promoted List table", Except for the "Expected New Level" Colunm, which replaces the "New Level" Column.
I am done with the job of the tables on VB.net (2010), but I need help in the area of the next form, which will first check the <b>Eligibility</b> of a staff for promotion, before assigning the staff for the next promotion. This has to deal with the "<b>Present year</b>" being worked upon and "<b>The Last Year of Promotion</b>" of the Said Staff.
First it has to check if the "<b>Present Year</b>" - "<b>Last Year of Promotion</b>" is greater than or equal to three (3). <b>If</b> it is greater than or equals three (3, Staff is eligible for Promotion and focus is set to the Promoted List table for the staff to be entered into the database, for the next promotion; <b>Else</b>, the staff is not eligible and an error message is displayed.
I'm using the VB.net 2010 for this project
|
|
|
|
|
Umm what is your expectation here, you have described a few tables and told us what you want to do but have not defined or asked a question.
We can't and won't build your app for you, you need to do that and when you have a specific problem with your code then we may be able to help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I was able to achieve that without the date time function.I made it as simple as i could do. But I am having a problem on deployment (publishing)of the program, to beable to run on any system, other than the one I developed with. when I tried to publish,it did publish, but if I try to access the forms that contains a link to the database, I keep getting error, "Microsoft ...... 12.0 or 4.0 (when I saved in 2002 - 2003 format), not installed on local machine. I want to send you the folder that contains the project, but can't, as I cant see where to attach files here.
|
|
|
|
|
What is the better way of indexing the foreign key?
Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_index (t1_id, t2_id), // this is my concern
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));
or
Create Table table3(
t3_id int not null auto_increment,
t1_id int not null,
t2_id int not null,
primary key (t3_id),
index IX_t1 (t1_id), //this is my concern
index IX_t2 (t2_id), //and this
constraint FK_t1 foreign key (t1_id)
reference table1(t1_id),
constraint FK_t2 foreign key (t2_id)
reference table2(t2_id));
This is for innodb tables, I dont have a broad understanding how the btree works. But as far as I know, the first table will save the indexes on single area while the second is not (correct me if I am wrong). And if it is what is the pros and cons if I will put it on a single index or multiple index?
Thanks.
modified 10-Feb-17 6:41am.
|
|
|
|
|
They are different ways and which is 'best' depends on your needs.
The first creates a composite index, and puts two fields in that list. The second one creates two separate indexes for those fields.
Are you going to be locating a lot of records by searching for t1_id, and when found, looking in that set for t2_id? Or are you going to be locating a lot of records by searching for either field?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
For now its just a concept table, for me to understand when to use the first or the second one.
can you explain to me when should I use the composite index or the other one? please. Thanks
base on your reply your saying that, if I use the t1_id and t2_id to satisfy a where clause frequently, I should use the composite key other wise I should use the separate index for every field. Am I right?
How about joining the three tables?
Thanks.
|
|
|
|
|
Think of it like a phone book: the data has a composite key of (Surname, Forename).
If you're always going to know the surname of the records you're looking for, then the index works.
But if you ever want to search for a particular forename, without knowing the surname, then the index is no help. You have to resort to reading through the entire book to find the matching records.
If you wanted to do that regularly, you'd create a separate index sorted by forename. That would make it easier to find records with a particular forename. But it would also mean you'd have more work to do when you insert, update, or delete records.
Which option you choose will depend on your data, how you're going to be querying it, and how often you're going to be changing it. For example, if you're only going to be using t1 as a lookup, and never searching for records with a particular t1_id, then there's probably not much point having an index on it.
It basically comes down to a combination of instinct and performance measurement.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks appreciated .
|
|
|
|
|
This Foxpro program keeps using spaces on the invoice number column in front or the data. Perhaps that was the norm back then before I started programming
E.G.
"70155" would be " 70155"
So I not sure if the column is fixed, or if every invoice number starts with a " " blank space.
e.g. " 155"
I wrote this placing a space in front of the invoice number, but afraid it may backfire on me when the invoice number grows larger
Dim c5 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01H.dbf SET FPRICE = 0.00 WHERE FINVNO = ' " & pFINVNO & "' AND FITEMNO = '" & pFITEMNO & "'", connection)
I thought using a wildcard, but I've been searching for a couple of hours and cant get things like $,% to work
If it ain't broke don't fix it
|
|
|
|
|
This works at the moment, I'll run with it today
Dim c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = " & pFPRICE & ", FAMOUNT = " & pFPRICE & " WHERE FINVNO LIKE '%" & pFINVNO & "%' AND FITEMNO = '" & pFITEMNO & "'", connection)
If it ain't broke don't fix it
|
|
|
|
|
Pad left (with blanks) all compared fields to their "defined lengths" (i.e. what's defined in the dbf), and then compare those.
That's your safest route (IMO).
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
I have a width of 74 for that column. I'm not sure what the units are.
I'll google it today.
If it ain't broke don't fix it
|
|
|
|
|
What do you mean by "Google" it?
The definition of the field is in the DBF; you can even determine that with code at run-time to make it generic.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
I think the width was 74, just not sure 74 of what unit.
If it ain't broke don't fix it
|
|
|
|
|
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
Using c3 As New System.Data.OleDb.OleDbCommand("UPDATE ARTRS01.dbf SET FPRICE = @FPRICE, FAMOUNT = @FAMOUNT WHERE FINVNO LIKE '%' + @FINVNO + '%' AND FITEMNO = @FITEMNO", connection)
c3.Parameters.AddWithValue("@FPRICE", pFPRICE)
c3.Parameters.AddWithValue("@FAMOUNT", pFPRICE)
c3.Parameters.AddWithValue("@FINVNO", pFINVNO)
c3.Parameters.AddWithValue("@FITEMNO", pFITEMNO)
...
End Using
Also, be very wary of your LIKE clause. If your pFINVNO is, for example, "1", your query will update every record where the FINVNO column contains 1, including '10', '21', '123456', etc.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It started out using parameters, and it's a Windows App.
But I wasn't able to match the parameters and started easter egg hunting to figure out why. Back to the case sensitive stuff and that extra blank char prefix.
So I wrote it the old school way to diagnose it.
I'll try and go back to parameters on that, but will have to use the ? in order for that.
It's suppose to be more FoxPro than SQL, like speaking pure FoxPro using VFPOLEDB
If it ain't broke don't fix it
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
