Database

Re: Rank over () / row_number over in sqlite

22-Jul-13 22:55

Did it even occur to you to type that into google[^], it seems to have been answered many time in great detail and 1 looks like it has a work around!

Never underestimate the power of human stupidity
RAH

jojoba2022-Jul-13 23:57

jojoba20

22-Jul-13 23:57

i have googled a lot but no good solution !
can give samll example ?

thanks in advanced

Product dataabase

DeerBear20-Jul-13 5:59

DeerBear

20-Jul-13 5:59

Hello!

I have the following problem: I need tips on structuring a product database. This will be a database of software products and what I specifically need help with is the following:

Say you have a software product in 4 editions: 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, i.e. if I put out a new minor version for one of the editions, I want to be able to track that there's been a change and what the change has been.

Is it clear?

Can you please advise and help?

Thank you!

A

Mycroft Holmes20-Jul-13 13:37

20-Jul-13 13:37

This isnot answerable in a forum post, you are going to have to do some research, here is some google foo[^] some where there is an entire collection of sample schemas for different industries. This may get you started but you are still going to need to do a lot of reading.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen20-Jul-13 23:24

Eddy Vluggen

20-Jul-13 23:24

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

DeerBear21-Jul-13 2:27

DeerBear

21-Jul-13 2:27

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 Smile | :)

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 Smile | :)

Mycroft Holmes21-Jul-13 3:36

21-Jul-13 3:36

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

DeerBear21-Jul-13 4:00

DeerBear

21-Jul-13 4:00

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

jschell21-Jul-13 10:00

jschell

21-Jul-13 10:00

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?

Mycroft Holmes21-Jul-13 12:57

How to issue setup to the different users according to their membership with windows application?

21-Jul-13 12:57

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

mehtabalikhan16-Jul-13 19:21

mehtabalikhan

16-Jul-13 19:21

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

Re: How to issue setup to the different users according to their membership with windows application?

Mycroft Holmes16-Jul-13 20:20

SQL Server file locations.

16-Jul-13 20:20

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

Septimus Hedgehog16-Jul-13 0:05

Septimus Hedgehog

16-Jul-13 0:05

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.

Corporal Agarn16-Jul-13 1:49

Corporal Agarn

16-Jul-13 1:49

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.

Septimus Hedgehog16-Jul-13 3:18

Septimus Hedgehog

16-Jul-13 3:18

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? Smile | :)

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.

Corporal Agarn16-Jul-13 4:18

Corporal Agarn

16-Jul-13 4:18

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.

How to replace string with stuff ?

Septimus Hedgehog16-Jul-13 4:44

Septimus Hedgehog

16-Jul-13 4:44

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.

caulsonchua15-Jul-13 23:03

caulsonchua

15-Jul-13 23:03

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

Richard Deeming16-Jul-13 1:37

Richard Deeming

16-Jul-13 1:37

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:

SQL

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

caulsonchua16-Jul-13 1:39

caulsonchua

16-Jul-13 1:39

HI, i used MS SQL server 2008

Richard Deeming16-Jul-13 1:50

Richard Deeming

16-Jul-13 1:50

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:

SQL

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

Mycroft Holmes16-Jul-13 12:44