Help get this topic noticed by sharing it on Twitter, Facebook, or email.

combine like rows when multiple children rows exist in related table.

I have a parent table, with equipment.
I have a related table with IPs in a 1 equipment to many IP addresses relationship.

I want the Equipment table to be searchable via IP so I modeled the IP table to the Equipment view.

now I have a few servers with multiple IP addresses and would like for only one to record to show up in the equipment list.

I hid the column with the IP address, this hides the IP address fine, I can search for IP fine, but I cannot seem to combine my server records into single rows.

Is there a simple way to manage this? or am I stuck with multiple rows from here on out?
1 person has
this question
+1
Reply
  • I have the same problem in a database that has a 1-to-many relationship between ships and their engines. One ship can have multiple different engines. Trying to educate users that this can give multiple entries for a ship when the tables are joined is a lost cause. Let's face it, pesky users are thick.

    My "solution" agreed with the customer, is to add a text (NVARCHAR(MAX)) field named 'Engines' in the master (ship) table that is a concatenation of main engine details from the child table. This field is maintained by the database via a trigger every time engine details change. This way the users can see all associated engine data in a single field in the ship table. This field is searchable in the usual manner so, everybody is happy, although the new field must be treated as read-only in the user interface - only the database engine can update it.

    You could maintain a text field in your master table named [IP_addresses] that is a concatenation of the IP addresses in the linked child table records. I'm not suggesting that it is "good" database design but: a) it works, b) it solves the problem in a way that users can understand c) the one-to-many structure is maintained for editing and so forth and d) it imposes minimal overhead. (assuming that your IP addresses aren't rapidly changing).

    Hope this helps.

    Paul
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned