|
As a STRONG suggestion you should devote a couple of days to a basic database design book. The consequences of getting your data structure wrong will cost you many multiples of that investment down the track.
Depending on the criticality of the project I would even consider getting a professional to do the basic DB design. There is also a site with sample schemas (I have lost the link) that will give you some excellent pointers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To somewhat piggy-back on the previous suggestions, I would recommend that you consider using an Identity column for your Primary Key and then using the PK to accomplish the links beween tables.
For Example:
Table: Buildings
BuildingID [int] Primary Key Identity (1-1)
BuildingName [VarChar] (25)
...
Table: BuildingRooms
BuildingRoomID [int] Primary Key Identity (1-1)
BuildingID INT
RoomName [Varchar] (10)
...
Table: Assets
AssetID [int] Primary Key Identity (1-1)
AssetNumber [varchar] (25)
AssetName [VarChar] (50)
BuildingRoomID [int]
...
By using using the Identity columns for the PK's of the tables, you can make your joins much more easily. You may also want to add some Unique Indexes on, for instance, the BuildingName, RoomName, and AssetNumber columns just to make sure someone doesn't accidentally add those items multiple times. 
Check out the following link regarding what is termed "normalization" . . . which is what we have suggested with regard to your tables.
http://www.dbnormalization.com/rules-of-normalization-i
|
|
|
|
|
I've spent some time looking into normalization and currently my tables should be in 3rd Normal Form. I also have my application talking to the remote SQL server via the Entity Framework, so I'm getting there. Thanks all for your suggestions, they were most helpful.
Rick...
|
|
|
|
|
I using three textbox(txtName,txtmobile,txtremark)and two skinid for this textboxs(snTextBox,snTextBoxNumeric)If cursor focused in txtname or txtremark mean defaultbutton is working,while its in txtmobile mean defaultbutton is not working because skinid for that textbox is different.
|
|
|
|
|
This is the database-forum. What does your question have to do with databases?
FWIW, you'd also need to explain which type of GUI you're building; ASP.NET, WinForms, WPF, Gtk# - otherwise you'll be getting answers that assume the wrong technology.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: What does your question have to do with databases?
Access 
I'm betting it is a miss post but some people call Access a database, much to my disgust!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you define a database as something that stores data, then by all means Access is indeed a database. It even stores data in a relational format and therefore qualifies to be called a Relational database as well.
|
|
|
|
|
I don't consider "stores data" as a definition of a database! Word, excel, xml, CSV, bin with screwed up paper in it all store data!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I obtain total number of years, months and days between 2 dates in a single sql query.
eg :
date1 = 2012-07-02
date2 = 2013-09-04
difference between date1 and date2 should show as "1 year, 2 months and 2 days"
Datediff function returns difference only by years or months or days etc...how can i do that
|
|
|
|
|
maxRazar wrote: difference between date1 and date2 should show as "1 year, 2 months and 2 days"
That's usually not done at the SQL-level, but in code. The SQL-statement would return the difference (just subtract one from the other). It's a bad idea to format data in Sql.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm with Eddy on this one do your formatting in the UI.
However did it occur to you to do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: do the datediff 3 times getting the Y/M/D separately and concatenate them as you want.
That won't work. Consider:
declare @start date = '20121231';
declare @end date = '20130101';
select
cast(datediff(year, @start, @end) as varchar(5)) + ' year, '
+ cast(datediff(month, @start, @end) as varchar(5)) + ' month, '
+ cast(datediff(day, @start, @end) as varchar(5)) + ' day'
;
The output will be 1 year, 1 month, 1 day, which is obviously wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
As stated, the query you showed will provide the wrong results, however, that doesn't mean datediff 3 times can't be used. There is just more to it.
Tim
|
|
|
|
|
You are right, a simple datediff 3 times will not do it, however with the application of a little imagination and some work with SSMS I know you can do this, I have seen it done in an example somewhere but dammed if I am willing to chase it down.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As other's have said, formatting shouldn't be done in SQL. However, if that is the requirment of the application, other replies do provide some insight.
Post what you have and then it can be reviewed and commented on.
Tim
|
|
|
|
|
I am in the middle of development on a WPF/SQL 2008 project.
As I make changes to the application I need to push changes to my client for testing. So I can create a ClickOnce for the binaries, but what's the right way to push DB changes?
Thanks
If it's not broken, fix it until it is
|
|
|
|
|
We use Red-Gates SQL Compare to deploy to UAT (production deployment is a more stringent process). Data compare to synch the master tables. I think it can be automated but we do a manual deployment as reports are built against UAT and not the dev database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm experimenting with encrypting some values in SQL Server (2012). This requires that I open the appropriate key(s) for the user/role. I haven't seen any information on doing this automatically.
I have written a procedure that will open keys by querying a table that associates users with keys. When I open the database in ADO.net via C#, I can simply check for the existence of the procedure and execute it before proceding.
[I see that procedures can be executed when the server starts, but that's not what I want.]
But it seems like there should be a standard/conventional/"best practice" way to do this at the database level so that the keys get opened even when I connect from SSMS or SSIS.
Can keys be opened automatically depending on the user?
Can a procedure be executed automatically when a user connects?
|
|
|
|
|
More of a poll than a real question, I guess, but here goes:
I have a database that contains information on how to, and how much it costs, to repair something. The cost is derived by adding the cost of all materials and all labor associated with a repair. The "defect" is stored in one table, and it's associated repair steps, materials, etc... are stored in other tables in a one to many relationship.
Long story short, every time I retrieve the cost of one of those defects, which is quite frequent, I have a UDF that figures the cost. I was thinking about storing that derived total cost as a field instead of processing the cost each time, as it would only be calculated each time the defect is changed or saved, vs. every single time someone requests the defect. I am generally a data purist and the thought of storing data I can derive chafes me, but it does seem like a lot of over processing.
What are your thoughts? Right now the table is only about 1500 records and it is unlikely to grow larger than that.
|
|
|
|
|
If the system performance is currently acceptable and is unlikely to change based on data growth, they why change the system?
However, if you view this as an opportunity to improve the system design and it will not adversely affect other work or performance, then change it.
|
|
|
|
|
|
Personally I'm with Tim, I consider your design the technically correct one. Technically correct is not always the best design, it is the one I prefer but if I thought storing the data would give me a better solution I'd have no compunction in changing it. Only you can asses the benefit to changing but I would think 1500 is a trivial number to deal with, add a few zeros and I'd look at it again!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Would you expect any speed different if I doing this with same computer hardware like I7,16GB ram and
case 1: ubuntu 64bits + java + mysql
case 2: windows7 64bits + c# + microsoft sql server 2012
modified 26-Jun-13 14:32pm.
|
|
|
|
|
Okay, what's going on here? I swear I've seen this question in here way too frequently. Let me guess, it's for a trading system?
Is this an exam question? An interview question? Is someone advertising contract work and asking this?
|
|
|
|
|
This is a more detail programming question already, not as general as before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
