Creating tables on a SQL Server

Tags:
SQL Server
SQL Server Scripts
SQL Server stored procedures
SQL Server tables
I am creating a directory of local businesses. I have found that my city is divided in to 4,816 business categories, which means I need that many tables in my database. Is there any way that I can create these table with a stored procedure, a SQL function, or a SQL script file? I want to use a temporary varible for the "table_name".

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

Do you think you need many tables as many business categories exists ? No, you don’t. You need one table to save all 4816 business categories. If you need to save more or detailed information for each business category, then you can create another table with the resting data, and create a relation between the first one and the second one, usign a code number or another data fields.

If this was misunderstood, please forgive me and tell me more about what you need.

Regards

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    I agree with Mario. You should have one table for the business, and a second table for the categories. The business table should have the id number of the category table in it.
    66,360 pointsBadges:
    report
  • ETCGMan
    Hi, and thanks for the response. Just want to make sure I understand. You are saying - create the categories table, then create another table where all businesses record information is placed along with a forieng key field, which links the records to the categories table? Would the second table ever be Normalized beyond Normal 1?
    40 pointsBadges:
    report
  • carlosdl
    "Would the second table ever be Normalized beyond Normal 1?" Yes, it could. Do you think it won't ? why ? What normal form do you think this design violates ?
    70,220 pointsBadges:
    report
  • ETCGMan
    It was my understanding that any duplication of data in a table puts the table out of normalization. For example - the city, state and zip codes of this table in most instances makes that the case however, I cannot see a situation where that would not be the case even if I separated these fields and put them in a separate table. The separated table [third table] would now be out of normalization. But I am encouraged by Mario's response since that was my original thought, to create a second table to house my data.
    40 pointsBadges:
    report
  • Denny Cherry
    You could create a seperate table called ZipCode with the ZipCode being the primary key, and other columns being City, State, Country. While this would be technically correct most people do not bother doing this as the increased management overhead isn't worth the disk savings.
    66,360 pointsBadges:
    report
  • Kccrosser
    A couple of things come to mind. You say there are 4816 business "categories". (That seems pretty large, but...) My strong suspicion is that any given business could belong in more than one "category" - especially if it is broken down that far. I would be looking at a database structure with at least the following tables: * Business (containing the "unique" information for that business - likely just a BusinessID and the Name - I usually like to add "extended" information [addresses, phones, contacts, etc.] in subordinate tables, keyed off the BusinessID) (primary key = BusinessID) * Categories (containing a list of unique CategoryID and CategoryName values) (primary key = CategoryID) * BusinessToCategory (containing a many-to-many association between BusinessID and CategoryID) (primary key = BusinessID+CategoryID) ("many-to-many" assuming that any business could belong in multiple categories, and clearly multiple businesses could belong in the same category) The comment about a "table" being normalized is a bit odd. An individual table has no "normalization" - it is the (unnecessary) duplication of information between tables that determines the normalization of the database. By definition, for a relational database to work, there must be some duplication of information between tables - otherwise, there is no way to link the tables in a query. When the amount of duplication is minimized, the database is considered to be highly normalized. The above example structure would be considered at least a 4th Normal Form database, as there isn't any way to reduce the redundant data (the ID columns) used to link the tables. (I will leave it to the computer scientists to argue whether it is 5th or 6th form - <grin>) On a minor technical note, beware of Zip Codes as primary keys - Zip Codes can span multiple "cities". In some cases, Zip Codes actually span State boundaries.
    3,830 pointsBadges:
    report
  • ETCGMan
    Hey guys, The response given by Kccrosser is the guidance I was looking for. Thank you all for helping me get through this. See you soon. Regards
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following