Advanced schema design question for Experts. Please help. Thanks

QUESTIONs:What schema is the best for high speed search for a classified web application?

Is our schema design looks OK? It is a STAR schema and will be used for OLTP type app. Is this OK? or Are we missing something?Please let me know if you need more diagramatic description.

BACKGROUND:Our group is making a classified website (like classified.yahoo.com) where people can place online ad to sell items.like cars, computers, electronics etc. Users will fill out webform for each category(car or computers) with allattributes of the item forsell to post an add.

Main 3 operations the web users will perform:Quick Searching(most frequent): category=car, subcategory=sedan, country=USA city=LosAngeles Zip=empty

Advanced Search(less frequent): User can include all fields\attributes or then can user a subset to query to do advanced search like :

category=car, subcategory=sedan, country=USA, city=Los Angeles, Year= in(00, 03), transmission=Auto, engine=V6, Maker=Honda, Model=Accord, color=Red and Price < 10000

category=car, subcategory=SUV, country=USA, city=NY, Year= in(99, 00, 03), transmission=Auto, Maker=Toyota, and Price between 11000 and 14500

Insert(least frequent): By filling out web form. For Car, the form will have different dimensions\attributes (year, make, model, transmission, mileage, color, price etc)

Current Schema design:Set of core dimension\lookup tables: stores core\common attributes for lookups eg. status={open, new, expired}, country={USA, Canada, India,...}

One custom Lookup table: for all custom attribute lookup: transmission{auto, manual}, engine type{V4, V6} for car, processor{PII, PIII, PIV), RAM(512MB, 1GB, 2GB} for Computer & so

3 Fact Tables:Main fact\Pivot table that stores all sell common attributes eg. price, title, year_made, post_date, expire_date, user_name, description etc.One fact table to store custom string, int, float, date field values of ads.One fact table to store custom dropdown field selection values

Concerns and issues:Looking at the schema, it seems to be a STAR schema with multiple fact tables where all core lookup tables connected to the main Pivot table andcustom lookup table connected to the 2nd and 3rd fact table.

Quick search only queries the Pivot fact table. While Advanced search query requires to join 3 fact tables. Both query requires to join 3 fact tables with alldimension tables(15 to 20 each having avg of 20 values) to get the look up names so that users sees text instead of ids. Search speed is the Main concerns. Insertion\update speed doesn't matter that much as that is less frequently done.

jennifer7290 wrote:

QUESTIONs:What schema is the best for high speed search for a classified web application?

Is our schema design looks OK? It is a STAR schema and will be used for OLTP type app. Is this OK? or Are we missing something?Please let me know if you need more diagramatic description.

Your model ("3 fact tables") is quite possibly the worst way to do this.
Let's think about it:
- You know what you're selling - Cars, Computers, etc.
- You know what attributes those entities have (Engine Type, Processor Type, etc).
- Users search for entities, not attributes ("give me everything witha V6 and a P4").
So why go with such non-orthagonal design? Subclass your entities:
Ads := ( Ad_Num, Ad_Type, Price, Start_Date, ...)
Ad_Type IN ('Auto', 'Computer', 'Boat', 'Service', ...)
Auto_Ad_Attributes := ( Ad_Num, Engine_Size, Color, ...)
Computer_Ad_Attributes := ( Ad_Num, Processor_Speed, ...)
...
Go with EAV and flush your data integrity down the drain and watch how absurdly slow your clasified system runs when people start using it.

|||Alex,
Thanks for reading it. What is orthogonal design concept? Is it database term or OOP term?
What is EAV stands for? How can I see some example design of your proposed approach or some tutorials, books will be really helpful.
Again, our emphasis is to track all classified information in a webbased tool with requirement of complex, dynamic query with HIGH speed in search.
Hope to hear from you.
Regards|||

I did google on it. It sounds like a bad design approach to me. The reasons are too many entities, so too many joins can't be avoided like the STAR schema.
How does your EAV performs better than STAR for classified system for a system with 20 metadata\entity\lookup tables??
HOW DOES YOUR MODEL PROVIDE HIGH SPPED IN SEARCH? Query speed depends on number of joins which can be done through denormalization.
In my case I actually have 1 fact table and 2 mapping intersect table (I mentioned as fact table).
Please see the link below:
Regards

http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm
Drawbacks of EAV/CR

Considerable up-front programming (wheel reinvention) is needed to do the tasks that a conventional architecture would do automatically. However, such programming needs to be done only once, and availability of generic EAV tools could remove this limitation.

EAV design is less efficient than a conventional structure for retrieving data in bulk on numerous objects at a time. (For object-at-a-time retrieval, such as through a Web-based browsing interface, the volume of data is small enough that the difference is not noticeable.)

Performing complex attribute-centric queries is both significantly less efficient as well as technically more difficult. This needs a query generator. However, most queries on scientific databases are relatively straightforward, and directed toward specific objects of interest.

For schemas that are relatively static and/or simple (e.g., databases for business applications, such as inventory or accounting), the overhead of EAV design exceeds its advantages.

If particular classes of data will be used across multiple databases in different domains, and/or there is a large amount of existing code that manipulates them, it should probably be left in its original conventional form. This is the reason why we have chosen to store bibliographic citations (that are not in external databases) conventionally. We similarly store certain genetics-related data conventionally because of a large body of code (from other databases previously created by our group, such as PhenoDB ) that performs computations such as Hardy-Weinberg frequencies and tests of significance.|||

jennifer7290 wrote:

Alex,
Thanks for reading it. What is orthogonal design concept? Is it database term or OOP term?
What is EAV stands for? How can I see some example design of your proposed approach or some tutorials, books will be really helpful.

Again, our emphasis is to track all classified information in a webbased tool with requirement of complex, dynamic query with HIGH speed in search.
Hope to hear from you.
Regards

EAV = Entity-Attribute Value, a totally unstructured design that involves Entities defined with Attribute/Value pairs:
EAV := (Entity, Attribute, Value)
{ 'CAR#342', 'Engine', 'V6' }
{ 'CAR#342', 'Color', 'Red' }
{ 'PC#4555', 'CPU', 'Pentium 4' }
{ 'PC#998', 'OS', 'Windows' }

This is effectively what you are doing with your "fact" tables, except you have three "fact" tables for some reason I really don't understand.

The design I mentioned is a very basic one. You have a table containing your base entities (advertisements) and tables containing subclass-specific attributes like Engine, Color, etc. You then make views for each type of entitiy:

Automobile_Ads :=
SELECT AD.Ad_Num, AD.title, AD.year_made, AD.post_date,
AD.expire_date, AD.user_name, AD.description , ...,
AUTO.Make, AUTO.Model, AUTO.Miles, ...,
FROM Advertisements AD
INNER JOIN Automobile_Advertisement_Attributes AUTO
ON AD.Ad_Num = A.Ad_Num

Find me all Ford Makes with a v6:
SELECT Make FROM Automobile_Ads WHERE Make='Ford' AND Engine='V6'

You would be very saddened to see how complex (and rediculously slow) such a query would be in an EAV table.

It's a very easy design to do: figure out all the entities (what you will allow ads for), then figure out all of the attributes of those entities. For each entity, create an attributes table as desribed above and a view described above.

|||

jennifer7290 wrote:

I did google on it. It sounds like a bad design approach to me. The reasons are too many entities, so too many joins can't be avoided like the STAR schema.
How does your EAV performs better than STAR for classified system for a system with 20 metadata\entity\lookup tables??

This is what happens when I POST before I READ. I misunderstood your design as EAV ... now that I read it again, I am a little lost ... could you elaborate by showing some tables ?

Advanced date/time queries

I'm a new member here and love the amount of knowledge available.

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

Thanks

GSS1 wrote:

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

You could answer this using the query:

Code Snippet

select t.phone_model, t.phone_manuf, count(*) as #faults

from t

group by t.phone_model, t.phone_manuf

GSS1 wrote:

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

This can be done by using query like below:

Code Snippet

select *

from Contracts as c

where c.expiry_date >= dateadd(month, 1, convert(varchar, CURRENT_TIMESTAMP, 112));

The "Inside SQL Server 2005" books are good ones for learning advanced TSQL programming.

ADO Table Schema doesnt show Primary Key

I'm trying to use the following code to examine table schema for SQL 2000. But when I do, the IsKey value is null or blank. I tried this on two different tables - a State table with a char(2) primary key containing the state abbreviation and a Trend table which has an identity column as the primary key.

Public Shared Function GetSchema(ByVal sTableAs String)As String Dim sbAs New StringBuilderDim oConnAs SqlConnection = OpenConn()Dim cmdAs SqlCommand =New SqlCommand("SELECT * FROM " & sTable, oConn)Dim drAs SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)Dim dtAs DataTable = dr.GetSchemaTable() dr.Close() oConn.Close()For icAs Integer = 0To dt.Columns.Count - 1 sb.Append(ic &":" & CNull(dt.Columns(ic).ColumnName) &" ")Next sb.Append(vbCrLf)For irAs Integer = 0To dt.Rows.Count - 1For icAs Integer = 0To dt.Columns.Count - 1 sb.Append(ic &":" & CNull(dt.Rows(ir).Item(ic).ToString) &" ")Next sb.Append(vbCrLf)Next Return sb.ToStringEnd Function

Here's the output from the function:

? datahelper.GetSchema("State")

"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType

0:StateCode 1:0 2:2 3:255 4:255 5:False 6: 7: 8: 9:StateCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3

0:State 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:State 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22

0:CountryCode 1:2 2:2 3:255 4:255 5:False 6: 7: 8: 9:CountryCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3

"

? datahelper.GetSchema("Trend")

"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType

0:TrendID 1:0 2:4 3:10 4:255 5:False 6: 7: 8: 9:TrendID 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:True 18:True 19:False 20: 21:False 22:True 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8

0:Description 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:Description 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22

0:Length 1:2 2:4 3:10 4:255 5:False 6: 7: 8: 9:Length 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8

"

Column 6 is IsKey but just displays 6: whereas IsIdentity displays correctly for Column 17. Can someone help me? Thanks in advance.

Found the problem. I need to use these command behavior values:

Dim drAs SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly + CommandBehavior.KeyInfo)

ADO Schema Rowsets and "identity"

Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
Hi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

ADO Schema Rowsets and "identity"

Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
> > I don't know much about ADO schema rowsets
> > But using T-SQL like
> > SELECT o.name, c.name FROM syscolumns c, sysobjects o
> > WHERE c.id = o.id AND (c.status & 128) = 128
> >
> > or
> > SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdentity)
> > you can easily return the info about IDENTITY existence.
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

ADO Schema Rowsets and "identity"

Hi there,
I'm working on an ADO generic application that tries to get as much info as
possible
from the ADO schema rowsets.
Is it possible - somehow - to know if a column is defined with the
"identity" property?
I cannot seem to find it ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.comHi
I don't know much about ADO schema rowsets
But using T-SQL like
SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128
or
SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
you can easily return the info about IDENTITY existence.
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:upL2I4ySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm working on an ADO generic application that tries to get as much info
as
> possible
> from the ADO schema rowsets.
> Is it possible - somehow - to know if a column is defined with the
> "identity" property?
> I cannot seem to find it ...
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||
> I don't know much about ADO schema rowsets
> But using T-SQL like
> SELECT o.name, c.name FROM syscolumns c, sysobjects o
> WHERE c.id = o.id AND (c.status & 128) = 128
> or
> SELECT COLUMNPROPERTY( OBJECT_ID('authors'),'au_lname',IsIdenti
ty)
> you can easily return the info about IDENTITY existence.
Yes, I know ... :-)
But this would defeat part of the "generic-ness" of the application. But if
this
the only way to go, then it's the only way to go ...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Martin
What is "generic-ness" of the application?
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:uC6C$AzSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
> Yes, I know ... :-)
> But this would defeat part of the "generic-ness" of the application. But
if
> this
> the only way to go, then it's the only way to go ...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> What is "generic-ness" of the application?
By using the ADO schema rowsets, I'm trying not to use any database engine
specific SQL and let the ADO driver figure it out.
So the application is generic... Or at least, should be.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com

ADO 2.7 with sql server using oracle syntax

Hi,

I've been tasked with investigating how we can migrate from oracle to sql server. I have successfully converted a typical schema with 97% success using SSMA and have a query regarding ADO and oracle syntax.

I have heard that the SQL server provider can interpret oracle syntax into its own syntax, but i am unable to find any reference/examples on the web. Is this possible? and if so could any kind soul please post some links.

Thanks in advance,
Michael

The SQL Server provider (SQLOLEDB or SQLNCLI for SQL Server 2005) does not directly interpret Oracle SQL syntax if that's your question but you can get at Oracle data from SQL Server in a linked server scenario or by using the OPENROWSET T-SQL command. See the following links for more information

OPENROWSET: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Linked Servers: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

Regards,

Uwa.