Table Structure, Query and Lookup Help

Ask for Help
1

As a complete outsider to the industry, learning table structure has been a process. Normalization and learning to use a SQL data base schema organizer has been key. Stuck here though…

My business provides services.

Price tier table with columns (ROW ID, Name, 1x Client Pay, 2x Client Pay, 3x Client Pay, 4x Client Pay, 1 Client Price, 2+ Client Price).

We offer several activities, each activity has one price tier. many activities can belong to same price tier.

Activities have multiple Billable Services. Billable services table has (ROW ID, ACTIVITY ID, PERSON COUNT - This is either Private, 1, or 2+). Essentially, clients get charged depending on if it is Private, 1 on 1 (without specific request for private), or 1 on X. This results in 3 different Prices. Staff however get paid based on how many people they have. Requested Private is X, 1 person is Y, 2 is Z, 3 is A, 4 is B. It is also not a linear scale as certain activities drastically change at different client thresholds (space available, supervision etc)

Originally, it appears all the data is there and available but the end result is it is quite difficult to query the price tier table and pull the data. You cannot have a value in the cell and have it lookup data from a specific row, in the coloumn that matches the value in the cell.

It must be a fundamental issue in structure, or im missing a way of looking up this data. Likely having pay and price in the same table isnt right.

I considered having a table that generates all possible combinations of Price tier and number of clients, and then each combo would have an ID, which could become the ID attached to any given service. This would be easily referenceable. What i cannot come up with is how to properly have this table generate itself.

I could create a complex workflow that added all rows but this feels wrong.

I have managed to acheive front end functionality through a long series of relation, lookup, relation, lookup, relation, lookup but it again, feels wrong.

Any suggestions? I can share the app as well but it has slightly different coloumn headings and labels. I adjusted here for clarity.

2

What does this mean?

What does this mean?

What does this mean?

I’ve reread your request multiple times and though I understand the words in English, I don’t understand some parts of your explanation.

I asked Claude and this is the answer I got. Naturally you would need to verify this answer or continue probing to challenge the AI’s answer:

Issues with Current Structure

The main problem is that you’ve combined multiple concerns in the Price Tier table:

  • Staff payment rates (the “Client Pay” columns)
  • Client pricing (the “Client Price” columns)
  • These vary based on client count, but in different patterns

This creates a rigid structure where lookups become complex, especially when trying to cross-reference a value in one cell with a specific column.

Suggested Restructuring

I recommend normalizing your database with these tables:

1. Price Tiers

PriceTierID (PK)
PriceTierName

2. Activities

ActivityID (PK)
ActivityName
PriceTierID (FK)

3. Staff Payment Rates

PaymentRateID (PK)
PriceTierID (FK)
ClientCount (1, 2, 3, 4)
Rate (amount staff is paid)

4. Client Pricing

PricingID (PK)
PriceTierID (FK)
ServiceType (Private, Regular)
ClientCount (1, 2+)
Price (amount client pays)

5. Billable Services

ServiceID (PK)
ActivityID (FK)
ServiceType (Private, Regular)
ClientCount (number of clients)

This structure separates the concerns and makes lookups much cleaner. For example, to find what a staff member gets paid:

  1. Look up Activity → Price Tier
  2. Query Staff Payment Rates with PriceTierID and ClientCount

Similarly, to find what to charge a client:

  1. Look up Activity → Price Tier
  2. Query Client Pricing with PriceTierID, ServiceType and ClientCount

This removes the need for “a table that generates all possible combinations” because the combinations are handled through relational queries rather than pre-generated rows.

1 Like
3

Wow!

Sorry for not making it clear, i think i would need several sentences to define each coloumn to a human. Thanks for trying.

Im amazed though that the AI understood EXACTLY what i meant by the coloumns, understood the crux of the issue, and gave a solution. I have tried asking AI and not got a good answer, although i guess in different words or a different AI.

Thanks for the help!

4

Should it be of any use to you, here was my prompt in Claude:

" [your entire post] "

From this explanation:

  • What tables are in the database and what entity does each table represent?
  • What are the attributes (columns) in each table and what do these entities represent?

No yapping.

It seems it didn’t actually answer my question, it answered yours in your prompt.

Do make sure you double-check its answer. AI is notorious for giving answers that both look good and are utter nonsense.

5

Also here is a good video on database normalization. Darren shares this one in the forum often and this is how I improved organizing my data. I refer to it on occasion, it’s a good refresher.

2 Likes