|
DeerBear wrote: I need tips on structuring a product database.
Learn the trade before you learn it's tricks. A decent database-design is normalized.
DeerBear wrote: Free, Lite, Standard and Enterprise.
I need to model all four kinds possibly in one table and not just that, I also need to make sure that changes are trackable
Having four different licenses doesn't complicate design - it's not even clear whether or not the licenses will differ in terms of their datastructure.
Tracability has nothing to do with the design; when you change it, make a note on a piece o' paper describing what you changed. Do that every time, and you'll have a list of changes.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi!
I am not new to databases, far from it, I was more
looking for speculative insight on how best to do
things. The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.
The more I look into it, though, the more I realise
there is no way to get minimal de-normalization so
that I can use the same table for two purposes
without putting an overloaded burden on development.
The tracking will have to be done in AUDIT tables with
INSERT triggers. All of the solutions I could think of to
avoid this are, effectively, unusable.
HOWEVER, if you happen to have ideas on how to do
that, I would be interested to know 
This is a sample of my structure(to give you an
idea of the path I am taking):
CREATE TABLE PRODUCTS (
ID INTEGER NOT NULL,
PRODUCT_NAME VARCHAR(255) NOT NULL,
PRODUCT_DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL,
DOWNLOADABLE INTEGER NOT NULL,
PRODUCT_DISCONTINUED INTEGER NOT NULL
);
CREATE TABLE PRODUCT_DETAILS (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
HIGH_VERSION INTEGER,
LOW_VERSION INTEGER,
EDITION VARCHAR(255),
DOWNLOADABLE INTEGER NOT NULL
);
CREATE TABLE PRODUCT_HISTORY (
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL
);
Oh and before someone starts whining about the lack of
primary or foreign keys: this is Firebird, those
elements are part of ALTER TABLE statements.
They're there but unreported for brevity
|
|
|
|
|
DeerBear wrote: The reason for that is that this product
will also be doing quite a bit of OLAP and that
usually means your database has to be de-normalized.
So not a complete newbie then - excellent.
Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.
While I don't know firebird I would not use the audit process/triggers spit to retain/create your history. I would build it into the business process where the application generates the history records (you may not want trivial changes to the data to create a history record).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Most OLAP solutions use a different database than the production DB and the data is denormalised periodically (daily) into said OLAP Db.
I am not an OLAP expert, by far, but in the very few experiences I've had where we
needed cubes, I've found that you don't always need another db.
What I am trying to achieve is a middle ground between a complete data
warehouse and a "normal" database. I don't mind, in this resepct, to lose less
meaningful data, as far as the most important data is dealt with appropriately.
I would like to add some new tables, prefixed by OLAP_ so that I know those are
related to my cubes, and do something like this:
CREATE TABLE OLAP_PROD_HISTORY(
ID INTEGER NOT NULL,
PRODUCT_ID INTEGER NOT NULL,
VERSION_HIGH INTEGER DEFAULT 1 NOT NULL,
VERSION_LOW INTEGER DEFAULT 1 NOT NULL,
EDITION VARCHAR(255) DEFAULT '' NOT NULL,
DISCONTINUED INTEGER DEFAULT 0 NOT NULL,
DOWNLOADABLE INTEGER DEFAULT 1 NOT NULL,
CHANGE_DATE DATE NOT NULL,
CHANGE_TYPE INTEGER NOT NULL DEFAULT 0
)
The original idea though was trying to keep everything in the ordinary tables but I do not
think that's doable in any easy way - unless someone comes up with a brilliant idea, that is!
Do you see my point?
Regards,
A
|
|
|
|
|
I must be missing something because that seems pretty trivial.
(If you want to track actual changes to code, rather than just something like release notes, then you should be using a version control system.)
And why does it matter if it is OLAP or denormalized? How many entries entries based on actual business cases (not developer fantasy) will it contain?
|
|
|
|
|
jschell wrote: How many entries entries based on actual business cases (not developer fantasy) will it contain
Damn missed that! This whole thing will be so trivial it does not matter how he builds the bloody thing!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anybody suggest me for my application scenario?
My Client is running a study center who want to give tutorials(Video and Pdf files) with a windows application to his students so that they can revise their subject at their home.
This application has to be divided in to two parts
1) A windows app that will be used by admin who will issue videos and pdf's(a windows app setup) to his students according to their membership. Membership means here suppose a student is studying English subject then he is allowed to get English (pdf and video's) data with application and if someone is studying English and Mathematics then he is allowed to get both subjects study material with the application(Study material is already prepared in pdf and videos)
I am storing both pdf's and video's in database so that they should not be opened directly or should not be passed to the others
2) A windows app that will run at student PC and will show only study material according to his membership
Please suggest me how should i issue such type of setup with Database from the admin windows application to the student so that he can study only his relevant material.
I am developing a Windows application on Visual Studio 2010 with Sql Server 2008R2.
Please Suggest
|
|
|
|
|
It will be just like any other app that consumes database data, you will make a connection to the database and retrieve the required information based on the user profile and display it in the appropriate controls (PDF viewer and video viewer).
In addition to your content storage you will need the user profile information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Chaps, I restored a database I was given. When I look at the database file properties, the MDF file is in the SQL Server folder C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\DATA but the LDF file is in a different folder. All the other databases installed have both files in the same place. Is it possible to "detach" the log file, copy it to the same place as the MDF file and reattach it so both are referenced in the same place? I've really got no idea how this occurred. I accept I must have messed it up somehow but I've no idea what I might have been done.
EDIT: It was my bad! I deleted the database and copied the two files to the same place. When I reattached the database, I must have clicked on the log file's eclipse (...) button. It was pointing to the wrong place but I was able to select the correct location. Sorry to have wasted time posting...
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
modified 16-Jul-13 6:21am.
|
|
|
|
|
In general it is not recommended to have the data and log files in the same place. Best practices would have them on separate drives.
|
|
|
|
|
While I'm not a total noob to sql server, I can't lay claim to fully understanding issues related to system admin and best practices. Could you explain why it's desirable to split the locations? 
The reason I'm interested in knowing more is because by default, new databases seem to be created with the log and mdf files in the same location.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
It has to do with disk access. For our setup we have different "disks" for data, log and tempdb. Unfortunately, I cannot find the link to explain this. I believe I found it while researching RAID setups.
|
|
|
|
|
I can see the advantage splitting the database files onto different disks. I'll mention it to our support team and see if they can fill me in with more information. Thanks for the heads-up about it.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Dear All,
May i know how to replace other table time in to other date time
Select EP_SCAN_DATE,CAST(STUFF(STUFF(EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) as SCANDATE FROM AcmkIMS.dbo.EP_SCAN_HIST
Result :
2012-01-06 04:39:19.000
Want actual result:
example time is 09:00 replace in
2012-01-06 09:00
kindly advise
|
|
|
|
|
Don't store dates as strings, and don't use string methods to manipulate dates. If you have any control over this database, you should change the EP_SCAN_DATE column to be a datetime.
You haven't specified which DBMS you're using. Assuming MS SQL, you can use something like this:
SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))
The DateDiff(dd, 0, X) will return just the date part of X, with the time set to midnight. You then use DateAdd to add as many hours, minutes and seconds as you require.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
HI, i used MS SQL server 2008
|
|
|
|
|
In that case, you might want to consider changing the column type to datetime2(0)[^], which is two bytes smaller than a datetime.
Alternatively, if you never need the time portion of the column, you could change the type to date[^], which is even smaller. You'd then need to cast the value to datetime2(0) before adding the time:
SELECT DateAdd(hour, 9, Convert(datetime2(0), YourDateColumn))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Let me reinforce what Richard has said, NEVER store date as text/varchar/char, ALWAYS use the correct data type.
Text will give you endless problems whenever you try and manipulate the data. The date and datetime data types will give you the tools to manipulate the data easily (not simply it can take a bit of thinking to work out how to achieve a given result).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I recently had the oracle 11g Express Crashed.
I have copied the .dbf files from C:\oraclexe\app\oracle\oradata\XE to other folder and reinstalled Oracle 11g Express edition.
The files are
Control.dbf
sysaux.dbf
system.dbf
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF
I want to remount the old database files, so that I can recover database.
Can you please tell me how can I do this?
-
Prathamesh
|
|
|
|
|
|
Maciej Los,
Thanks for your comment, But you can see the Question is Listed on OTN.
The similar Question you referred is also mine.
I am still in search for the solution.
-
Prathamesh
|
|
|
|
|
Hello,
I am using win XP with oracle 11g Express edition. I have a customized application written in c#.net and Oracle 11g express as backend.
It was all working fine till today, But now I am getting error.
While opening c# application I am getting ORA-1033 Oracle initialization or shutdown in progress
and if I try from command prompt.
sqlplus system/system123
Error : ORA-12560: TNS: protocol adapter error
tnsnames.ora content
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Oracle Services in services.msc
NAme Status
oraclejobschedulerXE -
OracleMTSRecoveryService - Started
OracleServiceXE - Started
OracleXEClrAgent - Started
OracleXETBSListener - Started
TNSPING O/p.
C:\Documents and Settings\User>tnsping localhost
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2
013 15:57:23
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (30 msec)
C:\Documents and Settings\User>
content of listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Listener Status :
C:\Documents and Settings\User>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:00
:46
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date 14-JUL-2013 15:07:14
Uptime 0 days 0 hr. 53 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\user-pc\listener\a
lert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=user-pc)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully
command :lsnrctl services
C:\Documents and Settings\User>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:02
:12
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_BBD1.ORA))
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_B074.ORA))
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:7 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Tried login using sysdba
C:\Documents and Settings\User>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 14 16:03:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
tried connecting from sql command line
SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 14 16:04:32 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> connect system/manager321
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
SQL>
Can you please suggest a solution? I have no fresh database backup.
-
Prathamesh
|
|
|
|
|
Hi,
I have table like below
col1
5
10
15
20
I need result like
Col1 Col2
5 05--9
10 10--14
15 15--19
20 20-24
Plz help
Thank You
Anil Banjare
|
|
|
|
|
How do you define ladder for the last value??
i.e. how do you get 24 in last row??
Apart from that, required query is -
SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2
FROM (
SELECT COL1
,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1
FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5)
)
;
And the output will be -
COL1 COL2
5 5--9
10 10--14
15 15--19
20 NULL
Thanks & Regards,
Niral Soni
|
|
|
|
|
USE tempdb ;
GO
DECLARE @ladderTable TABLE ( Col1 INT ) ;
DECLARE @incrementValue INT = 0 ;
INSERT INTO @ladderTable ( Col1 )
VALUES ( 5 ),
( 10 ),
( 15 ),
( 20 ) ;
SELECT TOP 1
@incrementValue = Col1
FROM @ladderTable
ORDER BY Col1 ASC ;
SELECT st.Col1,
CAST(st.Col1 AS VARCHAR(20)) + '-'
+ CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence
FROM @ladderTable AS st
LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1
AND ( st.Col1 + @incrementValue ) >= dt.Col1 ;
GO
Thanks,
Karunakar
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
