General Database Design Question

I have a table that will probably have 250+ fields - one business entity
with 250+ attributes.

I was wondering from a design, performance, and administration point of view
if I would be better off splitting this table into smaller tables by
grouping the logical work units...

Meaning...
Option (1)
tdtALL - 250+ fields  Contains all the entity information

Option (2)
tdtMAIN - 45 Fields Contains main entity information
tdtLUW1 - 20 Fields Contains logical unit of work 1 (1 to 1)
tdtLUW2 - 40 Fields Contains logical unit of work 2 (1 to 1)
tdtLUW...

Any comments regarding either practise would be greatly appriciated...

Thanks
Jeff.



0
Jeff
8/23/2001 9:50:25 PM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

2 Replies
730 Views

Similar Articles

[PageSpeed] 4
Get it on Google Play
Get it on Apple App Store

If you query and update this logical unities in separate moments, you should
really consider breaking it. Some issues are:

- Some databases doesn't support more than 250 columns in a table - you are
already near of this limit.

- Almost all databases organizes data in fixed-size pages, so if you have a
big record (sum of columns size) you will waste space in pages and loose
performance.

- If you update your logical unities in different moments, probably you will
have to define some columns as NULL. NULL is more complex to handle by
DBMSs, and frequently issues a performance penalty.

- Is conceptually better have the related information grouped together.

However, if you allways query and update this fields
at the same time, you should consider doing it, since if you broke the table
the queries will be more complex implying more application complexity, and
the data will not be together what means worse performance.

Wendell.

"Jeff Hersey" <jhersey@allnorth.com> wrote in message
news:2kuEaACLBHA.259@forums.sybase.com...
> I have a table that will probably have 250+ fields - one business entity
> with 250+ attributes.
>
> I was wondering from a design, performance, and administration point of
view
> if I would be better off splitting this table into smaller tables by
> grouping the logical work units...
>
> Meaning...
> Option (1)
> tdtALL - 250+ fields  Contains all the entity information
>
> Option (2)
> tdtMAIN - 45 Fields Contains main entity information
> tdtLUW1 - 20 Fields Contains logical unit of work 1 (1 to 1)
> tdtLUW2 - 40 Fields Contains logical unit of work 2 (1 to 1)
> tdtLUW...
>
> Any comments regarding either practise would be greatly appriciated...
>
> Thanks
> Jeff.
>
>
>


0
Wendell
8/24/2001 2:56:27 PM
We have a similar situation, and found that implementing them as separate
tables (with a 1:1 relationship) performed much better.  The key factor for
splitting them up was the timing of the updates.  There's a great deal of
"churn" on this table (it's an event scheduling application, so a single
event gets moved many times in a single day), so we kept the "primary" table
as thin as possible -- just the key and several critical attributes.  Once
the row is locked down with a consistent primary key value, we insert the
secondary tables with the additional attributes.  This prevents row splits,
and we didn't have to make all the secondary columns NULL allowable.

Paul Horan [TeamSybase]
Buffalo, NY

"Jeff Hersey" <jhersey@allnorth.com> wrote in message
news:2kuEaACLBHA.259@forums.sybase.com...
> I have a table that will probably have 250+ fields - one business entity
> with 250+ attributes.
>
> I was wondering from a design, performance, and administration point of
view
> if I would be better off splitting this table into smaller tables by
> grouping the logical work units...
>
> Meaning...
> Option (1)
> tdtALL - 250+ fields  Contains all the entity information
>
> Option (2)
> tdtMAIN - 45 Fields Contains main entity information
> tdtLUW1 - 20 Fields Contains logical unit of work 1 (1 to 1)
> tdtLUW2 - 40 Fields Contains logical unit of work 2 (1 to 1)
> tdtLUW...
>
> Any comments regarding either practise would be greatly appriciated...
>
> Thanks
> Jeff.
>
>
>


0
Paul
8/25/2001 2:52:42 PM
Reply:

Similar Artilces:

Design & Database Design Question
Hi I build computers and currently we use a access database to keep track of each system. Each system is generated a serial no and I input the different components and their serial numbers, then print out a system build summary sheet to go with the finished pc. We have two stores that build the computers.  Each store has a seperate database. Each store has unique system serial numbers such as Store1: DLX07-IN3145 Store2: DLX-DR2156 What I am wanting to do is create a web-based version in asp.net vb with SQL 2005 DB backend. And have both sto...

Database design question?
Hi, I have designed 2 different databases for an Asset Management System and want to know which of the 2 database designs is correct and which is wrong:Design 1 consists of one main table, Assets - consisting of IDs which are related to the IDs in the other 3 tables. Design 2 consists of one main table, Assets - not using id's, but using the actual names, ie instead of department_id 1 it would actually say ICT. My boss thinks that Design 1 is correct and design 2 is wrong, I think they are both correct but Design 2 has many advantages when coding as I never have to look up to s...

Database Design Question..
Hi Everyone, I have been working closely with major telecom providers in the past and I am about to redesign their major  billing system... Anyone have any links which can give me head start in designing a database and system for a telecom provider. ?? Cheers! Sunny NAGIProper Preparation Prevents Poor PerformanceDont forget to click "Mark as Answer" on the post that helped you. http://database.ittoolbox.com/nav/t.asp?t=349&p=349&h1=349Regards, Narayana Rao Surapaneni Architect | Author | MCSD for .NET | MCSD | SCJP www.msdeveloper.info ...

Database Design Question...
Say I have the following: Item Group is in a 1-to-Many with Item Range which is in a 1-to-Many with Item. Am I best: Putting the Item Group ID in Item Range Putting the Item Range ID in Item OR Putting the Item Group ID in Item Range Putting the Item Group AND Item Range ID in Item Is one a better design than the other? Is one illegal? I'm just thinking of user entry as well. In many ways it's the Item that is useful to the user (the two other tables are for reporting on ranges and groups - wider contexts the item belongs to). ...

Database design question....
I am building an application which is part directory (yellow page style). As I began to build the database and write up the design I found my self challenged with the following: In part of the db I have a table for companies, categories and subcategories. The problem is that some of the companies fall into more than 1 category and I am not sure how to effeciently design this. Can anyone share some thoughts or suggestions? ::The problem is that some of the companies fall into more than 1 category and I am not ::sure how to effeciently design this. What you need to make is a m:n...

Database Design Questions
I’m trying to design a database that allows the users to give each individual client/company unlimited addresses and salutations. I can build the design that accommodates this, but I cannot figure out how to handle them knowing which salutation to use with a mailing they might do to the clients. I have put the Company Name and Position (title) in the address table so that when doing a mailing the company name and title are associated with the company address being mailed too. But again, I’m not sure how they would choose a salutation if they have many choices. Looking for any of your ...

Database design question
When you have user logins and want to restrict data access for every user only to data relevant or owned by them how do you implement this? Example: When a user logs on, he will only have access to data that he himself have created or that have reading rights to which he qualifies. Lets say I have ten different tables and all data from all users are stored in these tables. Do I need an extra field in every table that denotes who has access to specific rows? What is the most sensible way to implement this scenario? We are using ASP.NET, C#, Windows Server 2003, SQL Server 2000 ...

Database Design Question
In our system we have several modules which are "optional" for purchase. In the past we've kept the table definitions for them in our main database. This has worked ok except in the case where 2 customers have different table definitions for the same tables because of a different business model. What we are considering is keeping the optional tables or tables that are customer specific in a different database and accessing them as proxy tables via the main database. This would allow us more plug and play type of access. The databases would reside on the same s...

Database design question
Suppose I have the following tables: Customers Warehouses Employees Each of these can have an address. My question is, what's the best way of representing this? I can think of two approaches: 1) Have 3 address tables: Customers ----> CustomerAddresses Warehouses ---> WarehouseAddresses Employees ----> EmployeeAddresses 2) Have 1 address table with three join tables: Customers ----> Customer_x_Addresses ---------\ Warehouses ---> Warehouse_x_Addresses ---------> Addresses Employees ----> Employe...

Database design question
I'm creating a simple CMS application and had a question on designing the table for the site pages and content.  Currently i have a table with the following fields: PageID, PageName, PageContent   When I render a page would it be best to query the table on the ID field or the PageName field? I'm wondering if its better to query on the name due to the fact that the ID values may change if the database is moved to another server as there is an aspx page for each page in the site due to the way the menu for the site was designed with images.   Thanks PageID would be your b...

Database design question
I'm currently working on a project started by someone else. All the data inserts and updates were basically stored in the concatenation of a series of separate sql queries located in different save subs of class objects and run at a final command execuation in a particular place, usually a final save sub in code behind. The project is now increasing a bit in size and going thru a major change on data content and business rules. The original design now becomes to me a maintennace headache. Should I propose to my boss to rewrite the project, convert all the s...

Database design question
Not strictly a PB problem, but I never get any help from the SQLServer newsgroups... We have an Items table, which contains master data about items. This data is fairly static. We need to store stock level information (quantity in stock, on order, allocated to customers, etc) for each item. We can either do this by adding columns to the Items table, or creating a new table, with a one-to-one relationship with Items. I can think of the following pros and cons: 1. Adding columns to Items. Queries will only have to select from a single table. Database is normalised. 2. New table ...

A database design question.
I would like to throw a question out to you guys. We have three concepts that Management thinks of as three databases. Users, clients, products. Users register to use products that we develop for clients. They all interact with each other and have constraints across the concepts. One idea thrown out was.. 'Hey why don't we have three databases?? There is too much going on for it to be one database.' Personally it seems like one system to me. Thus one database. What is the opinion out there about having three databases continually interacting with each other. What kind...

Web resources about - General Database Design Question - sybase.powerbuilder.database

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Five million customers affected by Vtech database hack
Toy and educational material seller Vtech has revealed five million customers were affected by a hack of its app database and has also suspended ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

Change your passwords: NexusMods suffers database breach
... security breach by posts on reddit. Cybersecurity firm REN-ISAC had apparently contacted several university IT departments about a database ...

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

VTech Admits Lack of Database Security Opened Door to Hack
A SQL injection, a common software flaw, was found to be the root cause in the VTech breach.

Animal Venom Database Could Be Boon To Drug Development
A new public database that catalogs animal toxins could spur the development of new drugs made with compounds found in animal venom.

Resources last updated: 12/8/2015 7:25:08 PM