Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Question about my database design and a query

Author  Topic 

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 07:37:29
Hi,

My DB Structure is like this:

Prefixes are:
------------------
tb = Table
Prd = Product
Loc = Location
Cat = Category
Comp = Company
Dtl = Detail
Desc = Description

Table and fields are:
---------------------
- tbProducts (iPrdID, iCatID, iCompID,
iPrdPrice, iPrdDetailID)
- tbPrdCats (iCatID, iParentCatID, iPrdDetailID)
- tbLocations (iLocID, iParentLocID, sLocName)
- tbCompanies (iCompID, iLocID, iDetailID)
- tbBrands (iBrandID, iDetailID)
- tbDetails (iDetailID, sDtlDesc, sDtlInfo,
sDtlDetailedInfo)

- tbProducts table contains product data, its category key,
its company key, its price data, its detail key)
- tbPrdCats table contains product category data and parent
category data for tree like structure.
- tbLocations table contains 'where is the company?' data.
- tbCompanies table contains company data and its location.
- tbBrands table contains brand information for a product with
using tbDetails table's key.
- tbDetails table contains template like information for other
tables are using.

****

In this structure, my database design is consisting of
many table which are have relationships with tbDetails table
for describing their data.

tbDetails table is like an information source table. Other
tables are getting their informations from this table.

Same field names in a table and in an other table is a
relation.

How the value of the design of my database? Is it ready for
millions of records? (I'm thinking only db structure, please
do not talk about the hardware specific issues)

And my second question is that:
-------------------------------
If one of my customer is want to display 'which company is the
most cheapest company in the los angeles that is selling
scanners?'

How can write a query for a state like this?

Thank you so much!
Best Regards..

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-06-13 : 09:14:52
In order to make an accurate judgement on database desing, you need to post actual ddl (create table statements, etc.). Please include all constraints, default, indexes, filegroups and sample data. Any attempt to pass judgement on your design would be futile given the number of assumptions that would be made to fill in the blanks. To start, there is no need to prefix your object and column as it just makes things harder to read.

<O>
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 09:36:52
I made a script file but its size is really high and i cannot post it..

So.. What about my second question?



Inanc GUMUS
1982
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-06-13 : 09:40:01
quote:
I made a script file but its size is really high and i cannot post it..

Then we really cannot help you...there's no point in making a guess without all of the facts, which is exactly what Page said in his post. There is simply NO WAY we can answer your second question without that information.

Just open the file, and copy and paste the contents into your post.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-06-13 : 09:50:52
Or hire your friendly neighborhood SQL Server consultant, so that you can get an anwser from someone who will be accountable for their recommendations....

<O>
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 10:15:21
quote:
Then we really cannot help you...there's no point in making a guess without all of the facts, which is exactly what Page said in his post. There is simply NO WAY we can answer your second question without that information.


Sorry but I dont think there isnt a way for answering to my second question because the question is really simply and you can imagine what the db table structure are...

Say that we have four tables which are named:

  • Products

  • Product Categories

  • Companies

  • Locations


With those tables I want to write a 'complex' query to the following question:
' Which company is the most cheapest company in the Los Angeles
that is selling scanners? '

I think this question can be answered without any knowledge about my whole db structure. I thought so many solutions but problems are not ends.
quote:
Just open the file, and copy and paste the contents into your post.

I tried that.. When I tried to paste whole contents of the file into the textbox is pasted succesfully. But when I pushed the 'Preview' button in the message editor of the forum, it did not display whole content that are wrote in the text box, I could saw only a partial section of my file..

Thank you.


Inanc GUMUS
1982
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-06-13 : 10:22:12
quote:

inancgumus
Starting Member

Turkey


Location ? or is that a description ?

Damian
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 10:27:12
Know what you are saying about my country!!

Yes! The description of my country in your language
is really like an animal description. But it is really
a country in the world..

Did you look my question?:)


Inanc GUMUS
1982
Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts
Posted - 2002-06-13 : 10:31:46
Your second question answer (being vague with answer cause your post is too) Something along the lines of

select min(p.iprdPrice), p.icompID
from tbProducts p inner join tbcompanies c on p.icompid = c.icompid
where tbLocations = 'los angles'


May need a groupby or order by statement there... The other opton would be to put the Min statement in a having clause.. mebbe like this, not sure

select p.icompID
from tbProducts p inner join tbcompanies c on p.icompid = c.icompid
where tbLocations = 'los angles'
having iprdprice = min(p.iprdPrice)


It is really hard to follow your mappings man, but hey.. I tried (unlike page and rob I like posting half answers and not the full garenteed one)



quote:
Location ? or is that a description ?

lol, cheers to that

-----------------------
The best answer = just do as rob or page47 say.


Edited by - M.e. on 06/13/2002 10:35:08
Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts
Posted - 2002-06-13 : 11:28:37
After a quick browse over your tables (I think I looked at atleast one of the 6 pages)... answer to question one = Yes
Answer to question 2 = sure why not

Why do I need to see all your views and foriegn key creation statements?



quote:
Say that we have four tables which are named:



Products

Product Categories

Companies

Locations


All I wanted was these 4 tables (which you did give.. I think, i don't think you actually left anything out of your posts there) Now more then likely you've lost page47 and rob (actually, my guess is rob fell off his chair laughing then went for )

Please remember, we're not getting paid to help you... To be honest I got no clue why we do... It just happens I guess (or we like . Just make it easy on us please.

Just read page47's response at the bottom.. Page is the only person I would expect to put begin and ends to his rants.
quote:
Dude, I've lost interest . . .

I haven't.. please keep ranting... your damn good at them

-----------------------
The best answer = just do as rob or page47 say.
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 11:47:47
Ok, there was no need to send views information but I think foreign keys are maybe required.. I sent the detailed information about my database, because, page47 was said:

quote:
In order to make an accurate judgement on database desing, you need to post actual ddl (create table statements, etc.). Please include all constraints, default, indexes, filegroups and sample data


I sent them because I asked before how the value of my db design is...

Now, I have deleted all the posts that is containing database scripts.. Now, I will post the create statements only.. Thanks all so much!..

CREATE TABLE [dbo].[tbCompanies] (
[iCompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[iUserDataID] [int] NOT NULL ,
[iLocationID] [int] NOT NULL ,
[iDetailID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbLocations] (
[iLocationID] [int] IDENTITY (1, 1) NOT NULL ,
[iParentLocationID] [int] NOT NULL ,
[iLocationTypeID] [int] NOT NULL ,
[iLocationDetailID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbPrdCategories] (
[iCategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[iParentCatID] [int] NOT NULL ,
[iDetailID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbProducts] (
[iProductID] [int] IDENTITY (1, 1) NOT NULL ,
[iPrdCategoryID] [int] NOT NULL ,
[iPrdOwnerID] [int] NOT NULL ,
[iPrdBrandID] [int] NOT NULL ,
[iPrdDetailID] [int] NOT NULL ,
[mPrdFee] [int] NOT NULL ,
[iPrdFeeVatRatio] [int] NULL
) ON [PRIMARY]
GO



Inanc GUMUS
1982
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts
Posted - 2002-06-13 : 13:20:49
somebody please tell me colleges or some books out there aren't teaching DBA's to use programming notation for all there databases!
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-13 : 13:38:03
quote:
(...) aren't teaching DBA's to use programming notation for all there databases!


Can you describe your comment bit more detailed? Why I dont use programming notation for databases?

Thank you


Inanc GUMUS
1982
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-06-13 : 14:18:48
Take a look at this thread, particularly the quote that Page47 provided by Joe Celko:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16750

The example given clearly demonstrates that the technique of prefixing column and table names actually makes the database design more difficult to understand. You should also read the original article by Joe Celko, he explains more about the problems.

In the olden days of computer programming a technique called Hungarian notation was developed. A variable would be prefixed with a letter or letters that described the kind of data that variable stored:

String variable: sVar
Integer variable: iVar
Float variable: fVar
Boolean: bVar or blnVar

This is the kind of prefix you are using in your database. The logic behind this technique was sketchy at best, it was more to promote ease of transferring code from one programmer to another. Another programmer would see the letter prefixes and know immediately which variables stored strings, integers, etc.

Unfortunately most people simply tacked on a prefix and did nothing to the variable name to describe what's in it, and you see A LOT of older code like this:

sVar1
sVar2
sVar3
sName1
sName2

OK, I know that they are strings, but I DON'T KNOW what those strings represent. The best I can do is figure out that a name is being stored in sName1 and sName2. Is it a person's name? A book title? A city name? What the programmer should do is use names like:

sFirstName
sLastName
sCityName

Now let's take off the prefix:

FirstName
LastName
CityName

Does the prefix really add meaning to these variable names? No. And any logical person would look at those variable/column names and deduce that they contain string data.

That's not to say that abbreviations aren't useful, but I find it a lot more intuitive to name a column PhoneNumber than dPhone or fPhone or f_Phone_Value. In the case of databases most people will look at a design diagram or entity relationship diagram and instantly be able to track the relationships between tables if columns are named without obscure prefixes or abbreviations.

If you choose a properly descriptive column name, then prefixes are unnecessary. If you're concerned that other people need to understand it, then you should properly document your database in addition to using descriptive column names. Using prefixes on columns is an old, archaic practice that should go the way of the dinosaurs.

Edited by - robvolk on 06/13/2002 14:23:02
Go to Top of Page

JustinBigelow
SQL Gigolo

1157 Posts
Posted - 2002-06-13 : 14:35:49
Inanc,

Based on your table DDL I dont see how you can answer your second question. All of your fields are setup as integers, there won't be a way to query on a city name unless the user just happens to know that 'Los Angeles' is LocationID #42. Same thing with Price (which I'm guessing is mPrdFee), I don't know anything about Turkish currency but if you are going to do price comparisons based on US dollars then integers won't work, you're answers will be off due to rounding.

quote:

select min(p.iprdPrice), p.icompID
from tbProducts p inner join tbcompanies c on p.icompid = c.icompid
where tbLocations = 'los angles'

May need a groupby or order by statement there... The other opton would be to put the Min statement in a having clause.. mebbe like this, not sure


M.E., the query above will need a group by, but doing so will return the lowest priced product carried by each company.

quote:

select p.icompID
from tbProducts p inner join tbcompanies c on p.icompid = c.icompid
where tbLocations = 'los angles'
having iprdprice = min(p.iprdPrice)


Closer but there is no compid in his products table unless compid is the same as ownerid. And if the ownerid is the same as compid then your join is redundant unless a text description is added to the companies table.

Justin
< />
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-14 : 04:45:58
quote:
Does the prefix really add meaning to these variable names?

I don't think so. The Prefix add meaning to the variables. Most least, I am thinking it so. The Prefixes makes my variables formatted and most understandable than writing only understandable variables names.

I've been always using Hungarian Notation + Understandable variable names like 'iaProducts'. Say me please, who can understand 'Products' variable is an integer array or string array without looking the declaration of this variable.

'iaProducts' in Hungarion Notation means 'Integer' 'Array' of Products. This is more meaningful to me than only writing as 'Products' -- is this variable is a string array, is an integer array or is an array??

Forget about it. Because this is a personal choice. But this choice becoming Sectoral choice when one is reading other's program code..

Whatever thanks for all comments.

But I can't solved my 'cheapest company' problem yet:(..



Inanc GUMUS
1982


Edited by - inancgumus on 06/14/2002 04:49:33
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2002-06-14 : 05:08:52
Marhaba Inanc,

What everyone is trying to say is that your database design is wrong. How do you get, (for example) the company name? where is the field which holds this value? what are the primary foreign key relationships?

We need this information to help you.... Otherwise this will keep going around in circles...

Peace

Rick
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-14 : 05:42:13
Merhaba RickD

I am holding all the informations (like company name, product name, company info, product detailed info etc.) in my table named tbDetails. This table is acting like as an information source table.

As I have said before Same field names in a table and in an other table is a relation. Except one! tbProducts.iPrdOwnerID has a relation to tbCompanies.iCompanyID table which means tbCompanies.iCompanyID is the foreign key of tbProducts.iPrdOwnerID field.

tbDetailID is:
----------------------------------------------------
CREATE TABLE [dbo].[tbDetails] (
[iDetailID] [int] IDENTITY (1, 1) NOT NULL ,
[sDtlDescription] [nvarchar] (127) NOT NULL ,
[sDtlDetailLessInfo] [nvarchar] (255) NULL ,
[sDtlDetailedInfoPath] [varchar] (127) NULL ,
[sDtlImagePath] [varchar] (127) NULL ,
[bDtlIsActive] [bit] NOT NULL
) ON [PRIMARY]




Inanc GUMUS
1982


Edited by - inancgumus on 06/14/2002 05:44:53
Go to Top of Page

rihardh
Constraint Violating Yak Guru

307 Posts
Posted - 2002-06-14 : 06:01:36
I really hate people who shoot first and ask later. It's nothing wrong with jumping headover into something BUT!!!, I think that everybody had to read some manuals or books during development.

No hard feelings Inanc but you should first check out a book on relational databases and basic DB design.
Go to Top of Page

inancgumus
Starting Member

40 Posts
Posted - 2002-06-14 : 06:15:23
rihardh, I have read so many books and articles and I have enough experience about relational databases. I just asked for 'what is the value of my db design is?'; I not asked 'should I read some books?'

Thank you for your comment.

Edited by - inancgumus on 06/14/2002 06:17:21
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts
Posted - 2002-06-14 : 06:55:22
Inanc,

Your tbCompanies would require one record for every tbDetails, whereas this should surely be the other way around. So I think your design is incorrect from what you have posted of it...

Peace

Rick



Edited by - RickD on 06/14/2002 06:56:24
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site