SQL Server Performance

help with table design

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Mar 2, 2010.

  1. shankbond New Member

    I need some help with table design
    I am having a table here
    CREATE TABLE dbo.lists(
    columna INT IDENTITY(1,1) NOT NULL,
    columnb VARCHAR(1000) NOT NULL
    Now the columnb is suppose to have values such as ab1, ab3, ab6, ab90,......, ab100,....
    There is no limit that is in some rows there can be one id in some there can be can be many.
    These values are the primary key of another table say:
    CREATE TABLE dbo.sometable(
    columna VARCHAR(10) PRIMARY KEY,
    datab VARCHAR(50) NOT NULL,
    datac VARCHAR(50) NOT NULL,
    Can someone suggest a better design of table dbo.lists or is it perfect?
    Just while googling I saw the term Junction table, Yes this is what I am trying to make the table dbo.lists, But I still have a question, there will be one columna Id repeated I guess not less but hundred times, cause there can be say hundreds of sometable.columna Ids, is this be viable? and may not cause any performance issues?
    Would I have to create the referential intigrity through Stored Procs or other easy way other than triggers?
    Any help shall be appreciated
  2. Adriaan New Member

    When working in a relational database, you really should start thinking in terms of normalization.
    Having a repeating value (multiple key values) in a single field means that these key values are effectively hidden --- your interface will need to process the whole string value to find a relevant key.
    Having the repeating values as distinct rows in a junction table means that those key values can be indexed, so your interface can simply instruct SQL Server to do the searching, and SQL Server has an index to help speed things up.
    Think of it as keeping things organized ...
  3. shankbond New Member

    Thanks Adriaan, One last thing I will have to make a referential integrity via Stored Procs?
  4. Adriaan New Member

    The main method for enforcing Referential Integrity is to add Foreign Key constraints.
    You can use triggers, but you will need to produce T-SQL code yourself, where a constraint would be a lot less work.
    Because an insert/update/delete event will be checked against constraints before the triggers fire, and since constraint violations always result in ugly ODBC error messages that cannot be suppressed, people sometimes prefer to use triggers.
    You may also run into situations where you cannot add an FK constraint because it would result in a circular reference. In such a case, your only option is to implement the logic in a trigger.
  5. shankbond New Member

    Thanks Adriaan, One last thing I will have to make a referential integrity via Stored Procs?

Share This Page