SQL table length with spaces. OK or NOT??

[rtshort]

I am working on a VB App and have long table names and sometimes a lot of spaces in them. Here a couple of examples.
***************************
I/B Mercruiser GM145I/L4 110 63-66 Gas Carb 1537370 Thru 2044033

O/B Chrysler 102HD 10 76 Gas Carb All
***************************

I know the table names are long and have a lot of spaces, but it makes my app do exactly what I want. I use the Mid function in VB to pick out exactly the names I want to fill up ComboBoxes on a form. It has been working perfectly so far. I think I read that the Max TableName in SQL 7.0 is 128 characters.

Given the above information, would this be considered OK or a little close to the edge??? Rob
Just my $.02.

 

[tlbroadbent]

I'm sure I wouldn't name tables in that manner. I think the naming scheme violates several naming conventions. It certainly makes the SQL more difficult, in my opinion. Future maintenance will probably be a nightmare. However, it is your project and SQL Server can handle the names. Good luck. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[arrowhouse]

I agree with Terry,

In general, making things easy in VB (or whatever) is no excuse for poor database design & I think that includes the naming convention.

It can save time when your dealing with the problem, but in my experience it often cost far more time & money to maintain & develop the system from then on.

You can use sp_validname (see books online) to validate your existing table names. 128 is the max length in 7.0 as far as I'm aware.

My advice is don't make a rod for your (or some poor dba's) back.

Sorry if this isn't the answer you wanted

 

[rtshort]

Thanks for the comments guys. I do appreciate the response. Rob
Just my $.02.

 

[Dilyias]

Hey, I'm just wondering why you would need table names like that? That almost seems like data that would be in fields.

Or if you are having someone pick names of a table you could have the list go in a seperate lookup table that points to the table names TBLxxxx i.e.

'TBL0001' , 'I/B Mercruiser GM145I/L4 110 63-66 Gas Carb 1537370 Thru 2044033 '
'TBL0002' , 'O/B Chrysler 102HD 10 76 Gas Carb All '
'TBL0003' , 'Another long table name ... '

And use the lookup table for your form, drop down list, whathave you in vb... just a suggestion, again i may be way off base from what you are trying to do here.

 

[rtshort]

Well I'm just getting into this phase of the app I'm working on in VB 6.0. I'm using the naming scheme to fill Combo Boxes on a form. I am open to suggestions though. This is what my sql statement in VB looks like if you are familiar with VB.
This is the first Click Event sql statement on the form looks like.

sql = "Select * from SysObjects Where Name Like '%" & cboEngType.Text & "%'"

This is the last Click event sql statement on the form.

"Select * from SysObjects Where Name Like '%" & cboEngType.Text & "%'" & "And Name Like '%" & cboEngMake.Text & "%'" & "And Name Like '%" & cboEngModel.Text & "%'" & "And Name Like '%" & cboEngHorsePower.Text & "%'" & "And Name Like '%" & cboEngYear.Text & "%'" & "And name Like '%" & cboEngFuelType.Text & "%'" & "And Name Like '%" & cboEngInjectionType.Text & "%'" & "And Name Like '%" & cboEngSerialNo.Text & "%'"

Since each of the cboClickEvents fill the information on the next, when the last is reached it is narrowing the field down considerably. All of the different categories in the table names fall into place just right so I can use the Mid Function in VB to extract just the part of the table name I need to fill the combo boxes.

This was the only way I could think of to give each table a distinct name, and still easily fill up the Combo Boxes. There will be a LOT of tables when this is finished.

Hope I'm making some sense here.

Laterz Rob
Just my $.02.

 

[rtshort]

I should mention, I am writing a little VB app using Masked Edit Boxes to create the tables so the naming(spaces) will stay in the correct, crazy order I've created them in. Also to enter parts and prices into the newly created tables, I'm putting that on the same little app too.

Since I am doing the whole project with a little help entering the data and creating the tables(Hence the little app) I'll be the only one going crazy with the names.

Rob
Just my $.02.

 

[Dilyias]

Hmmm, that seems like an innificient way to do things, putting strain on the server, if I understand right -

How about creating a lookup table, perhaps with a table id, and a text field with the name, like I mentioned above. Fields TableName and Text

Then you could use vb to concat the strings into one big string, then search for this string in the text field, like such:

longstring = cboEngType & " " & cboEngMake & " " & cboEngModel & " " & cboEngHorsePower & " " & ..... & " " & cboEngSerialNo

(I ommited the .text here, it is optional)

then execute "SELECT TableName FROM MyTableNameLookup WHERE text = '" & replace(longstring,"'","''&quot & "'"

You can then reference the TableName returned and query that table, etc..

I did that replace(..) so it will change any 's in the text field to double quotes '' so you won't get a sql error. (I'm just writing this off of my head so be sure to check syntax, etc..) This approach should greatly speed things up, maybe you could try having a clustered index on the text field as well and see if that speeds things up, assuming you will have a lot of records there.

Hope these ideas* help!

* Since I'm no expert these are ideas only. Not a guarantee.