Member Avatar for Mitja Bonca

I have:

Table1: Service:
- IDService
- ServiceName

Table2: Order:
- IDOrder
- IDServiceFK (foreigh key)

I have database table called "Service", in which I store service names.
Then I have another table called "Order", in which is the foreigh key of Service.

Services are shown in the listView control. The listView shown checkBox control. So far I had only an option to choose only ONE item from the listView (only one item could be checked), because I have only one column name for it in the table Order (only one place - IDServiceFK). And the ID of this item (service`s name) was written into Order table (into service foreign key).

But I would like to change the code, which will allow user to select as many services from the list as he likes.
The problem is that I do not know how to do the Order table (how to insert all the IDs of selected services into Order table).

Should I do:

Table2 Order:
- IDOrder
- IDServiceFK1
- IDServiceFK2
- IDServiceFK3
- IDServiceFK4
- IDServiceFK5

and limit the selection on 5. If more then 5 selectred, user gets a warning message that he reached the max of selection.
In case if user does select less then 5, I just send a DBNull to the others.

What do you think? How can I get rid of this problem? And how in case if I want that user selects 100 items? I can not do 100 column names just for the IDs.

Would be idea of creating another table, where will be stored only foreign keys of selected items a good solution?

Like:

Table1: Service:
- IDService
- ServiceName

Table2: Order:
- IDOrder
- IDOrderListFK

Table2: OrderList:
- IDOrderList
- IDServiceFK1
- IDServiceFK2
- IDServiceFK3
- IDServiceFK4
- IDServiceFK5

Please help.

Edited by Mitja Bonca because: n/a
  • 2 Contributors
  • 1 Reply
  • 154 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by apegram
Member Avatar for apegram

There should be a many-to-many relationship between services and orders. You would implement that with a joining table.

Service
IDService
(+whatever other fields relevant to the service)

Order
IDOrder
(+whatever other fields relevant to the order)

OrderedServices
IDOrderService
IDOrder
IDService
(+whatever other fields relevant to the line item)

So for each Service that is ordered, a record will be inserted into the OrderedServices table. The key is that many services will result in many rows, not columns.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.