|
So there's this guy I know and he wants me to teach him data modeling in a nutshell. He asked me to just give him a fast run-down on Primary Keys, Foreign Keys (the difference) and normalization. He's pretty smart so a link to something brief but technical is fine.
...
...
Okay! I'm lying! The guy is me and I haven't done data modeling in years. I'm trying to remember this simple thing. If I have a table called Sequences as an example and I have a table called OrderProcessing and 6 other tables that use Sequences.SequenceId (or a number representing that number in the Sequences table). Do I just make all of them FK's to the Sequences.SequenceId PK?
Does that make sense? I totally need a drug test and I know that...
My name is Maximus Decimus Meridius, Commander of the Armies of the North, General of the Felix Legions, loyal servant to the true emperor, Marcus Aurelius. Father to a murdered process, husband to a murdered thread. And I will have my affinity, in this life or the next. - Gladiator. (Okay, not quite Gladiator but close.)
I work to live. I do not live to work. My clients do not seem capable of grasping this fact.
Ancient of days! august Athena! where, Where are thy men of might? - Lord Byron
|
|
|
|
|
|
First, let me start off by saying, "The Codd, the whole Codd, and nothing but the Codd. So help me Codd."
Second, let me tell you to normalize to at least the 3rd form normal.[^]
Oh yeah, a word of warning, oh mighty data diddler, once you master normalizing mere mortals will require you to denormalize in order communicate with them...
EDIT: Duh! I guess answering your question would help. The answer is "yes".
"We are all repositories for genetically-encoded information that we're all spreading back and forth amongst each other, all the time. We're just lousy with information."
- Neal Stephenson
|
|
|
|
|
Jerry Hammond wrote: Oh yeah, a word of warning, oh mighty data diddler, once you master normalizing mere mortals will require you to denormalize in order communicate with them...
Unless it is for performance reasons, VIEWs can help with this.
|
|
|
|
|
I have an application that is creating its own databases at run time. This application has a dedicated file structure for storing the database mdf and ldf files under its application directory under "Program Files". The issue I am having is this (sorry for lengthy explanation):
Originally I created a temp folder on my c drive such that I had c:\temp\ for storing these files. I could deploy my application with no problem what so ever.
I have recently moved the temp folder into my application's root directory such as this: c:\Program Files\MyApp\Working\Data\.
Should be no problem as the folder has an exact same setup as the c:\temp folder. But, the database will not allow databases to be created in the folder or any directory under "Program Files".
I have found that if I go into permissions and give the folder under "ProgramFiles" the sqluser$machinename$serverinstance group permissions it will work fine.
So my question is WHY? and HOw can I skirt this issue at deployment?
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
hello,
my problem has got to do with XML and SQL, so i didn't know where to post my problem.
But now to topic: SQL Server 2005 has many different column-types e.g.: int,varchar... and also XML. I chose XML for one column in order to save a xml-document (not file) in it. But i always receive an error, that XML cannot be parsed into string. So i tried a little bit and made it working with ' xdoc.InnerXml '.
But how can i parse that string back to an xml document?
I hope i understandable explained my problem.
Thank you
chrstn
PS: Sorry for my bad English
|
|
|
|
|
I am using DTS to import data from an Excel Spreadsheet. One column, which is typed as VARCHAR(100), will import numbers but when the data in that column is formated like:
HMPG:051022:RSK:IDIGO2SPI9S-WWD_JJTXWE1G7UOTXCFZLQ:04252007:102844PM
OR
013970:MKENDE6548:MARTHA
I am getting a NULL imported in it's place.
Any ideas?
Thanks,
Bryan
|
|
|
|
|
I believe this may be an issue with your Jet Driver for Excel. What happens is that it decides within 8 records by default what type the column is. There is a registry change that will need to be made in order to overcome this, if it is the same cause that I am describing.
Solution from the link follows[^]
onedaywhen wrote: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.
ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:
Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
I had made the registry edits but had forgot the IMEX=1...THANKS!!!!
It's always the simple things that get you!
Note to anyone else having this problem:
When you are using DTS you can change the IMEX=1 by going into Disconnected Edit and adding it to the Value of the Extended Properties under your source connection.
Thanks,
Bryan
|
|
|
|
|
I am attempting to improve a current VB.NET 2.0 program that originated in VB6 with disconnected recordsets and arrays by replacing the loops that look for matches with a DataTable.Find call. This seems to work all well and good except that the current and old program both use some time of in memory storage until it is time to write the output to a file.
The in memory storage was a disconnected recordset and is now...you guessed it...a datatable. I noticed that the program memory usage with the recordset stayed around 20 K while the program with the datatable seems to increase with each record dealt with so when the program has read in 500 K records it is using 700 K memory. Why is this and is there a way for me to minimize the datatable memory impact? I want to avoid using the VB6 style.
One tidbit - I currently do not have the option to utilize a table in SQL for this.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Are you really only keeping a dataTable or do you have a dataset as well? The dataset has a lot more overhead. Here is another question. If you don't need the datatable functions then why not use a dataview? That should be more light weight and should allow you to do what you need to do. You can start with a datatable and then keep the view with dataTable.Defaultview.
Ben
|
|
|
|
|
I build the blank datatable in the beginning and add to it so I need that functionality. As for the dataviews, do they allow for finding rows?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Yes, there is a RowFilter property. When you set this property the only rows you will see are the ones that meet that filter criteria. If you want to see all the rows again set the rowfilter to "".
Hope that helps.
Ben
|
|
|
|
|
So if I want to hold 1 million records (made up number) in memory which would utilize less memory, the datatable or the dataview?
What is it about the disconnected recordset that allows it to store the exact same amount of records (1 million (made up number)) and utilizes 2.5% of the memory that the datatable utilizes?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.
Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.
Ben
|
|
|
|
|
kubben wrote: Yes the DataView will take up less space. How much less. I don't know you would have to try it. I would sugest starting with the datatable build the columns, take the defaultView and put it into a variable. I think the issues with the datatable have to do with its ability to save state. It remembers the original state of the database before any changes were made. After changes are made if you want them to persist then you have to do AcceptChanges. So the datatable is most likely keeping two versions of the record which would at least double the size in memory.
Note If you set a Sort for the dataview you and use a Findrow method based off that sort column. Just thought you would like to know.
Alright, let me say that I have appreciated your help so far, I never thought to use a DataView for this purpose. The speed was reduced by 30 minutes so it is 46% faster that it was before BUT the memory usage is still unreal. The reason I say unreal is because I watched it with the disconnected recordset and it never rose much above 20,000 KB. This process now uses 850,000 KB at the late end of the process. Do you have any additional ideas of what to do to lower that other than using the disconnected recordset?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Unfortunately no. Microsoft just added a lot of features to DataSet and DataTable, that makes them big. I suppose they figure memory is cheap. By the way, have you seen the RAM memory prices on NewEgg, sorry but I was blown away the other day. Ok, back on task. Using the dataview is the best you are going to get with those structures.
Ben
|
|
|
|
|
kubben wrote: Unfortunately no. Microsoft just added a lot of features to DataSet and DataTable, that makes them big. I suppose they figure memory is cheap. By the way, have you seen the RAM memory prices on NewEgg, sorry but I was blown away the other day. Ok, back on task. Using the dataview is the best you are going to get with those structures.
Is there anything .NETificationally wrong with using the disconnected recordset to save on memory usage? The .FindRows has really cut down on the total time it takes to run so that is great because the old way uses arrays and loops until it finds a match but MAN the memory usage...and no more RAM is not the solution.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy) "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Personally, I wouldn't do it, but then again I don't totaly understand your whole problem and solution. There may be other options. If you create a class that meets the columns you need and if you are using .net 2.0 you could have a generic collection of that class. I would imagine that would take up less memory.
Hope that helps.
Ben
|
|
|
|
|
Hi chaps,
I currently use Data Tables / Rows for my OR Mapping, but it's a bit weak.
I've browsed a whole stack of OR tools in wikipedia, but i'm not sure which one to go with?. Could anyone give me any recomendations or their preferences?
I do have a few requirements:
Handles Nullable Types
Doesn't generated stored procs
Ideally, i just want a collection of objects that relate to each other in code, that have support classes with standard CRUD functionality but can be customised. Additionaly, the ability to specify custom functions for validation.
It's been something that has been perplexing me for a long time now.
Any advice appreciated
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
DevExpress Persistent Objects (XPO).
Castle ActiveRecord.
I've used both and they are both good - although they do require a bit of a mind-shift.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
SELECT pag_id, 3 + 5 AS expr1, CASE WHEN isnull(expr1 + , 0) <> 0 THEN pag_expire ELSE 10 END AS expr2
FROM dbo.cms_page_loc
query a valid one on sql server , its work
fine in MS access.
please ignore the logic of the query, i just want to know how to validate
an evaluated expansion i.e(expr1)
When you get mad...THINK twice that the only advice
Tamimi - Code
|
|
|
|
|
expr1 does not exist yet. It is part of the metadata of the result set. To evaluate it in the following column definition you need to either repeat the expression that created expr1 or to evaluate it in a subquery so that it is passed up to the main query.
|
|
|
|
|
Dear All,
This is related to IList concept in .net 2.0,
I have Ilist property i.e CourseOptions,
There one storedProcedure That return result like this
ID VALue Text
1 Averages YES
2 Honor Rolls NO
4 Marking Period Totals YES
8 Class Ranks NO
16 Transcripts YES
32 PRC YES
64 Attendance NO
128 Print On Schedule NO
256 Print On Matrix NO
512 Offered Course NO
1024 Nat'l. Hon. Soc. NO
2048 No Marks NO
8192 Junior Hon. Soc. NO
I Want to get all the values of Text column.
These columns i need to bound to IList of CourseOptions I can doit
Pls help me in this
Thanks N Advance
|
|
|
|
|
shakeela wrote: please help me in this very urgent
I offer help. I don't priorities because someone said their problem is urgent. To each of us our own problems are always the most urgent. If your problem is truely urgent them I'm sure you wouldn't mind paying for a support call. However, you are here, so it obviously isn't that urgent.
You will also have to make your request clearer. I don't really understand what you want to do. Are you looking for SQL code or are you looking for how to get the data in the .NET application? Or do you already have the data in the .NET application and need to coerce it into an object that implements IList?
|
|
|
|