Creating Relationships on the fly.

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)

Creating Relationships on the fly.

Author

Topic

asaxena
Starting Member

8 Posts

Posted - 2003-12-11 : 03:29:30

We are developing a Profiling Engine that allows companies to profile themselves for finding trade matches.

The Basic company data is stored in a table called tbCompany where CompanyId is the Primary key.

As these companies belong to various different sectors, they need to provide different type of data for finding good matches. To ficilitate this, we create multiple sectors. Whenever a sector is created, a new table is created on the fly for that sector.

These sector tables store the sector specific details of the various companies that fall withn that sector.

We wish to define the CompanyId field of tbCompany in the sector tables as a foriegn key. However, as the sector tables are created we are not in a position to do so.

Is there a way of creating a FK relationship in a table which is created on the fly?

Thanks

Aseem Saxena

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-11 : 04:43:12

I would want to question the need to create tables on the fly in the first place. If you need to correlate companies to sectors, what's wrong with a table design like the following?


Table: Company
CompanyID
CompanyName
...other fields

Table: Sector
SectorID
SectorDescription
...other fields

Table: SectorCompany
SectorID
CompanyID
Attribute1
Attribute2
Attribute3
--above attributes will differ for each sector
...other attributes

But to answer your question, you can define a foreign key on a column while creating the table itself. Just add the Foreign Key constraint to the CREATE TABLE statement.

Owais

We make a living out of what we get, but we make a life out of what we give.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-11 : 04:56:37

I would agree with Owais. Don't create tables - have tables existing that will allow any attributes to be saved and retrieved.

If you are creating tables via the application why not rely on the application to maintain integrity rather than create further database structures?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

asaxena
Starting Member

8 Posts

Posted - 2003-12-11 : 05:11:42

Dear Owais,

Thank you for replying to me.

The problem is that we do not know the sectors that will be created. The customer wishes to create the sectors himself. Also, the attributes for each sector will be created by him using a web based interface.

Also, the total number of sectors will be in the range of 1000-1200. Thus if each sector has say 10 attributes, we will have 10,000-12,000 columns in the Sector company table. MS SQL only allows maximum of 1024 columns in each table. So, accomplish what you are suggesting, we may have to join some 10 tables. This will make add / edit / deletes and searches almost impossible to manage.

I am not really a technical guy... but more of a functional person. Could you please provide me with the syntax that I ned to embbed in the stored procedure for creating the FK relationship on the fly?

Kind regards,

Aseem Saxena

quote:
Originally posted by mohdowais

I would want to question the need to create tables on the fly in the first place. If you need to correlate companies to sectors, what's wrong with a table design like the following?

Table: Company
CompanyID
CompanyName
...other fields

Table: Sector
SectorID
SectorDescription
...other fields

Table: SectorCompany
SectorID
CompanyID
Attribute1
Attribute2
Attribute3
--above attributes will differ for each sector
...other attributes

But to answer your question, you can define a foreign key on a column while creating the table itself. Just add the Foreign Key constraint to the CREATE TABLE statement.

Owais

We make a living out of what we get, but we make a life out of what we give.

Aseem Saxena

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-11 : 06:27:24

>> Also, the total number of sectors will be in the range of 1000-1200. Thus if each sector has say 10 attributes, we will have 10,000-12,000 columns in the Sector company table

No no no.
Each attribute can be held a separate row in a table.
Certainly every sector would be a separate row.

Think you need a database designer to look at your proposed solution.
It so happens I'm available at the moment :).

Note that any database can be converted to one which has a single table with three columns id, type, data (think about it - could be even less if you want). Means that any statement that suggests that you need too many columns is incorrect.
Have a look at crm packages - they tend to have structures that allow users to create entities and structures - the table structures are fixed though and it is all done by entries in attribute tables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

asaxena
Starting Member

8 Posts

Posted - 2003-12-11 : 06:45:15

>>>No no no.
Each attribute can be held a separate row in a table.
Certainly every sector would be a separate row.

OK... let me see if I get this right:

What U suggest is a table structure like this:

Company Id (FK)
Attribute Id (FK)
Attribute Value.

The problem is that each sector has different attributes. Also, attributes could be of diferent types: Text, Number, date, Boolean, and Range.

If my understanding of your proposed solution is right, then I do not see as to how will it address the situation...

I beg pardon for my ignorance in advance :(

Aseem

Aseem Saxena

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-11 : 06:52:55

CompanyAttribute - add ean entry to this table to add an attribute for the company - it does not hold any data
Company_id ,
Attribute_seqno ,
Attribute_name ,
Attribute_type

CompanyAtributeData - this holds the data for each attribute - it could be as character data but you could have several fields depending on type
Company_id ,
Attribute_seqno ,
Attribute_data

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-12-11 : 07:11:16

nigel's way is the way to go, if you run DDL on the fly you will soon have "the database that god forgot".

We have about the same scenario in a number of systems here, and it works like a charm.

Be sure not to use SQLVariant for your data fields, it's pretty slow :( You'll be best of using varchar for everything or splitting it up into one column per datatype.

asaxena
Starting Member

8 Posts

Posted - 2003-12-11 : 07:28:45

Yup... this makes sense.... THANKS!

Aseem Saxena

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-12-11 : 08:01:31

Bet you end up with CompanyAttribute rows like
('CompanyX', 15, 'Contact1', 'varchar(100)')
('CompanyX', 16, 'Contact2', 'varchar(100)')
('CompanyX', 17, 'Contact3', 'varchar(100)')
('CompanyX', 18, 'Contact4', 'varchar(100)')
('CompanyX', 19, 'Contact5', 'varchar(100)')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources