help with table design | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

help with table design

Hi,
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?
edited:
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

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 …

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

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.

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

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |