SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

CoC SQL gods, I have a question, is there a way to apply an EAV-like schema without the SQL heresy that is EAV. I have a client asking for what is basically a product database that can be compared, the problem is that there's many different products that I want to be able to define dynamically (eventually offload this to their administrators). It's going to be used to track price and feature changes through telecommunication providers (this is for pricing and mostly marketing/decision making reasons, and it's not going to be outside facing, so performance is not so critical (few clients, some patience). Ed: The comparisons are going to be only between the same products from different companies.

So basically I need to define my data models dynamically. I was thinking of using this sort of modeling.

An example of my Data Definition (only products themselves need to be dynamically define):
Product1 (Voice lines) (edit: there was a rogue paste here, gently caress my sensitive touchpad)
- Company - this is really metadata
- Contract Length - same
- Actual Price
- Extra Line Price
- Facility MRC
- SLC Price
- Regulatory fees
- Total Fees
- Total Facility MRC plus Fees
- Notes

Product2 (Long Distance Plans)
- Company - this is really metadata
- Contract Length - same
- Local Calls
- LD US
- LD Int
- TF Services
- CCs
- Timeframe
- Per Minute Price
- Notes

An example of atoms:
Company 1's Product1:
- Company: Company1
- Contract Length: 0yr contract
- Actual Price: $350
- ELP: $310
- SLCP: $660
- RF: $30
- TF: $440
- TFPF: $900
- Notes: Does not include feature X or Y

Company 1's Product2:
- Company: Company1
- Contract Length: 0yr
- Local Calls: True
- LD US: True
- LD Int: True
- TF Services: True
- CCs: True
- Timeframe: 24hr (fractioned 30/6)
- Per Minute Price: $.05/min
- Notes: <blank>

My guess is that SQL is just not designed for this and it's better to use something like an RDF datastore for this, and if this is the case, does anyone have any resources for learning to use RDF?

deimos fucked around with this message at 20:14 on Dec 12, 2007

# ¿ Dec 12, 2007 17:34

Adbot: ADBOT LOVES YOU

# ¿ May 2, 2024 20:38

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

m5 posted:

Come up with a generalized model. If you're going to be able to implement some sort of comparison, you'll be doing that anyway.

Gah forgot to mention, the comparison is only between companies on the same products, the problem with a generalized model is that the products are very different, some might have 35 different attributes (this is pretty much the case with wireless plans) and might grow dynamically (wireless plans are constantly adding features, think of alltel's favorites and other stuff).

# ¿ Dec 12, 2007 17:59

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

Victor posted:

On EAV: if you have SQL2005, it becomes a lot prettier with PIVOT functionality. A lot prettier. Dunno about how badly it performs...

Yeah postgresql has the tablefuncs contrib for this, I was just wondering if anyone had a better idea. I am guessing I will make a product table and just foreign key an EAV-based table to it.

Another question, do I separate the "design/definition" data and the actual data or do I put them in the same table with some sort of flag?

Since I don't think I'll ever have to do anything with the data other than load and save (in terms of RDBMS interaction, because I won't ever have to search it) I could even make it a blob and just use XML/JSON to store it (yes, this is relational heresy, I know, but I don't care).

# ¿ Dec 12, 2007 20:29

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

Victor posted:

It really depends on your software. If you go the table inheritance approach, then it's possible that running DDL is part of standard application operation -- that's always viewed with extreme amounts of suspicion. However, just like any powerful functionality, there are ways to use it that will probably be OK.

Woop, I was looking up on postgres' tablefuncs and found: http://developer.postgresql.org/pgdocs/postgres/hstore.html

This is sweetness, you can even create indexes on the data, as perverse as that is.

They also have an XML contrib to run xpath through the data (the same thing DB2 has) if I decide to go the XML way.

edit: django + postgreSQL + hstore it is, onward with the heresy (it's actually fairly easy to integrate into django because hstore's functions return something that can be eval()ed into a python datastructure :black101:

deimos fucked around with this message at 22:19 on Dec 12, 2007

# ¿ Dec 12, 2007 21:22

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

code:

SELECT myfield, COUNT(*) AS Recordcount 
FROM mytable 
WHERE MATCH (myfield) AGAINST ('orange apple' IN BOOLEAN MODE);

# ¿ Jan 3, 2008 21:48

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

Victor posted:

Shouldn't there be some documentation on MySQL full text search on how to do this stuff? I would be shocked if this were the first time such functionality has been required.

That would make too much sense. Problem is that their documentation isn't that awesome.

Victor posted:

Clever, but I don't believe that documentation actually addresses "how to do this stuff".

I wasn't being smarmy, their documentation sucks.

deimos fucked around with this message at 22:25 on Jan 7, 2008

# ¿ Jan 7, 2008 19:31

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

imBen posted:

That really shouldn't make much of a difference at all.. that query is nothing.. sounds like your machine must just really be a piece of poo poo.

Sort of like when I had Dreamhost hosting my MySQL databases...

Or it's misconfigured (using my-small.cnf or something similar).

# ¿ Jan 12, 2008 20:28

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

Golbez posted:

Is there any site or book that goes in to the best way to construct good search engines in PHP/MySQL? Surely fulltext isn't the end-all be-all to it... right?

Step 1: use a text indexing search engine like Lucene

# ¿ Jan 19, 2008 22:23

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

I present The Ark of the Covenant (SQL 2005):

code:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SYS_getBody]
	@id numeric
AS
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @strSQL varchar(4000)
  SET @strSQL=' SELECT strBody as Body '
  SET @strSQL=@strSQL+ ' FROM tblBodies '        
  SET @strSQL=@strSQL+ ' WHERE 1=1 '      
  SET @strSQL=@strSQL+ ' and intId in 
                            (select intContentItemId from tblcontent 
                            where intcontenttypeid=5 AND
                            intCollectionId = (select intRelatedCollectionId from tblstory 
                                               where intStoryId = ' + convert(varchar,@id) + '))'
  SET @strSQL= @strSQL+ ' ORDER BY intElementId asc' 

  exec (@strSQL)
  IF (@@error <> 0)
  BEGIN
      ROLLBACK TRANSACTION
      RETURN @@error
  END
  ELSE
  BEGIN
    COMMIT
    RETURN @@error
  END

This, my fellow goons, is what I have to deal with on a system that I have no control over. This is about the BEST code out of all the stored procedures, I don't want you guys to melt when you see the really bad code. I am not even a good DB programmer.

We complained to the provider of the above code that if we hired a people farm they could search our database manually faster than this stored procedure. To which they replied "do you have any pointers on how to make it faster?".

I am extremely frustrated right now and can't verbalize any reply to the question. Short of telling them to gently caress off and fire all their staff that has worked on the stored procedures, what resources can I give them on how not to be a worthless loving SQL programmer?

edit: table breakage fix

# ¿ Feb 3, 2009 23:33

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

code:

SELECT	best_nid		AS bid
FROM	bc_bests
WHERE	ranked_nid IN ( SELECT 	term_node.nid	AS nid
			FROM		term_data
			LEFT JOIN	term_node
			ON		term_data.tid = term_node.tid )
LIMIT	20;

# ¿ Feb 8, 2009 15:25

Adbot: ADBOT LOVES YOU

# ¿ May 2, 2024 20:38

deimos: Nov 30, 2006; Forget it man this bat is whack, it's got poobrain!

I have a database which is fairly bad in terms of organization and need to clean it up. It basically contains information about Puerto Rico restaurants. As of right now all the information is kept in a single table, so there's a ton of data duplication.

I propose the following example, a restaurant called "La Ensalada" which has 6 locations appears 12 times on our restaurant table. 6 for each location and then 2 for each translation. There's some information that pertains to the restaurant chain itself (the kind of food it serves and the price, for example). Most of the information is part of the location, and some of this info needs to be translated since our site is bilingual.

My idea is splitting it into 3 tables:
Restaurant
RestaurantLocation
RestaurantLocationTranslation

The issue comes because I have some fields that need to be both in the Location and each LocationTranslation (think of it as a default and then a translated override) like the street address (I need a default in order to do geocoding). Leading to some, but not much, data duplication.

Keeping in mind that some of the translated columns need to be full text indexable, but not the ones that are duplicated (ie. Description doesn't need a default in RestaurantLocation, needs to be full-text but street address doesn't need full-text but needs a default).

The question is, am I doing this right?

Or should I maybe drop the RestaurantLocationTranslation table and instead use XML or something. This is in SQL Server 2005 (could switch to 2008).

# ¿ Mar 16, 2009 16:38

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'