CODEWITHSUNDEEP
sql-servert-sqldatabase-designschema
Naor
Naor

Reputation: 24083

avoid duplication of table

I have table with data about tasks like Id, Name, Date, WorkerId, VehicleId (and more 50 fields).

I would like to build orders module that will aloow users insert tasks as an order. An order contains subset fields from task, for example WorkerId and VehicleId only (as I said - there are more then 50 fields in tasks table). Since each user can choose his fields definition that compose an order - I have to duplicate the tasks table and name it orders table. Because the huge size (in terms of fields) of tasks table, I want to avoid duplicate it. Is there any possible way to store order data that not include create huge table like tasks table?

P.S. I don't want to store the orders in tasks table because: 1. there will be many orders that might affect performance. 2. there are many many tasks already.

Upvotes: 0

Views: 220

Answers (4)

PerformanceDBA
PerformanceDBA

Reputation: 33718

If you have 50 fields in Task, that table is un-normalised. If it is a "very duplicate system", the database is not Normalised. As you seem to be aware, un-normalised tables are difficult to extend, they place severe restrictions on functional extension.

Before you add Order, and relate them to Tasks or components of Task, thereby compounding the duplication, you need to Normalise Task first. That will result in several normalised tables (Person, Vehicle, Worker) which Task relates to, and it will then be simple to add Order and relate Order to those tables.

Upvotes: 1

joelt
joelt

Reputation: 2680

If I understand right, you want to create a new table, but you don't want it to have 50+ columns. Is that right?

You could do something like Orders(ID, Configuration) where Configuration is some string representation of all the optional data. Or you could do something like Orders(ID, FieldID, FieldValue). In this case, if an order had three of the optional fields, it would show up as 3 different rows in the table. Both approaches create challenges when you want to query them. I think I would bite the bullet and just have the 50 column table.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

It's hard to tell for sure from your description, but it sounds like you want to treat the columns from the table "tasks" as line items of an order.

ord_num  line_item    task_attribute    attribute_value
--
15       1            WorkerId          35
15       2            VehicleId         101-345-2A3574

Having said that, though, I have to say that

  • it does make sense to me to let users order tasks, but
  • it doesn't make sense to me to let users order one or two columns from a table of tasks.

Especially to just order one or two id numbers from a table of tasks--WTF does that mean, anyway?

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838276

You could add a field is_order to your tasks table and set this to true if its an order or false if its a task.

You may also want to rename your table to show that it can contain either orders or tasks.

Upvotes: 1

Related Questions