One Table for different usecases?

#1

I got a DB where I have 3 tables.

  1. t_user
    which contains users. Each User needs to specify his 3 branches. Like , Electronic, Architecture or Paintings.

  2. t_adverts
    contains ads which can be assigned to different branches. Like above, Electronic, Architecture, Gaming, Gambling, etc.

  3. t_branchdepencies
    contains these informations which user is assigned to which branch. And each advert can be also assigned to branches.

So note: User fits into max 3 branches. One Advert can also fit into branches.

Now my logic problem starts here. How can I select all adverts where the user matches his defined branches? Do I need to add the same branchdepencies table twice as a JOIN ? One where I match the t_user defined branches and then again and try to match the t_advert defined branches?

image
image.png1428×941 97.4 KB

Smth like that? Its the same table, but used twice

image

#2

if you give the table an alias you can add it multiple times

(Having said that,i they are the only tables i suspect the real problem is your database design is flawed, you need two more tables to do it properly)

#3

Okay thank you so much! But should I do 2 separate tables? I got it working now with one table. What is the PRO / CONTRA and how would you do it?

#4

Just dragged my backside out of bed after a heavy night of drinking and in need of breakfast. Will post an optimised database design later when my head stops pounding. I suspect you have fields in your table like advert1, advert2, advert3 etc? That’s not the way to do it in the relational database world

1 Like
#5

Haha me too brian. Had rum, lots of beers and shots yesterday. But today is a good day, cause nobody disturbs you. I just use t_branchdepencies for identify which user has which branch and which advert has which branch. I think its not a bad db design. ??? Bad correct me please. Wanna do a clean DB design before going live. I guess most people go with two different tables to make the assignment.

In my case if I want to identity which branch has an advert, I just define them in t_branchdepencies and leave user_id stuff empty.

And on the other way if I want to identity which branch is assigned to a user I just define them in t_branchdepencies and leave this time advert_id stuff empty. As shown in my example. But there you see a bad example, cause I show user_id filled also on the last two ROWS

#6

So just to clarify

t_users defined the user
t_adverts defines the advert
t_branchdependencies defines the advert category i.e. Electronic, Architecture, Gaming

so a user can have and advert which can exist in multiple branches (categories) and that user him/herself can belong to 3 dependencies (categories) as their area of expertise

Do the dependencies have top match or can a user belong to say Electronic, Architecture, Gaming but the advert belong to say Electronic, household, furniture (i.e. only 1 common branch)

#7

You understood it perfectly. No there is no Top group or hierachical tree. If I understand it like what you ask. But is that the right way? Its working now, but used DISTINCT mode for getting quick results. Maybe I need to check variations with JOIN type…

PS: If I would group it i would add a „parent“ column in branchdepencies.

#8

I would use some intermediate tables to hold the relationships between the primary tables, users, advert and dependencies. That way you could probably get every type of data view you need through simple inner/left/right join.
This also allows a user or advert to reside in any number of groups, you could limit this to three at present but any increase would not need any table restructuring

image
image.png2568×1048 155 KB

#9

@Hyperbytes Woow thank you for that preparation of Design! I‘m studying now your nicely prepared db relations and what benefits I would have. I still dont get the benefit, maybe you can explain it in other words? I dont understand that with „no need to touch“ my table if a new branch comes. Now i have it similar but just in one table :thinking:. BTW: I forgot to mention that my branch category names are in a separated table. So its like:

t_users defined the user
t_adverts defines the advert
t_branchdependencies defines relations (for user and or advert)
t_branch has branch names

Maybe I‘m still not over my hangover…

But maybe the simplest thing is just that my Db design with a single table isnt NORMALIZED enough or my design is kinda quirk :smile:. I think thats the proper term in my case. Your Design is normalized and mine isnt. :thinking::slightly_smiling_face:

#10

OK, adding that 4th table to the discussion changes things somewhat, much closer to a normalised design.
You could use 1 table to hold both user and advert dependencies provided the table had some sort of marker to indicate which table the dependency refers to
So, based on my design above, the user_dependency_link and advert_dependency_link tables would become 1 table with a additional field, something like DependencyType, where for example a value “U” would indicate a user dependency and value “A” would indicate an advert dependency

I may have misunderstood but based on the initial information but It appeared to me you are effectively trying to build 1 to many relationship via multiple one to one relationships within the database query itself. This only makes both the design and the query more complex.
With a properly normalised design, relationships are one to one or one to many so the limits are either 1 or table maximum
By using this design the limit on the number of dependencies to 3 becomes a app imposed limit rather than a database design imposed limit
If you decided at a later date to up that limit to 4 or 5, it is just a simple change to the app itself, no database restructuring or relationships need to be changed.
That’s the way database design should be ideally.
In my experience i have found that failing to normalise properly in the early stages can lead to major headaches later if the system grows significantly
The normalised version i showed simplifies the query and is, in my view, therefore easier and more efficient in it’s implementation

1 Like