MySQL Forums - Database Design & Data Modelling

Copying table structure to manage large number of rows. (no replies)

Philip Parker — Tue, 21 Jan 2025 22:12:35 +0000

I'm finishing an application and preparing for the possibility of 10 million active users each year. I'm looking at one table that will have 100's of rows per user. I'm a programmer not a DBA and thought duplicating that table structure 50 times could work. Each User would be assigned a permanent table decided by their user_id. Those tables would not JOIN or connect with each other but will JOIN with a few other tables. This is only to manage the very large number of rows expected. Is this a valid design or am I missing something? While researching I see Partioning mentioned. Should I be looking into that? Below illustrates what I have in mind. Thank you.

User
=======
1 John
2 Adam
3 Jane

TableNum = ceiling( user_id )

Table1 ... Table50
======= =======

testing available timeslots (1 reply)

Dimitris Papageorgiou — Thu, 23 Jan 2025 09:53:09 +0000

I am building an appointments scheduling software and I have made a rather complex query to get the available timeslots for booking. My issue has to do with testing. What is the route to take in order to catch all possible edge cases. How to go with that. I assume nothing. Following is the query and the DDL for the 3 tables involved.

SELECT time_fifteen_minutes.TimeColumnMinutes AS AvailableTimeslots,
CASE
WHEN store_open.open_time_b IS NULL THEN "First"
WHEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100")
THEN "First"
WHEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time_b
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time_b, "100")
THEN "Second"
ELSE "Unknown Shift"
END AS ShiftLabel
FROM time_fifteen_minutes
INNER JOIN store_open
ON
CASE
WHEN store_open.open_time_b IS NULL
THEN time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100")
ELSE
(time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time, "100"))
OR
(time_fifteen_minutes.TimeColumnMinutes >= store_open.open_time_b
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(store_open.close_time_b, "100"))
END
LEFT JOIN (SELECT
TimeColumnMinutes AS A
FROM time_fifteen_minutes
INNER JOIN appointments
ON ADDTIME(time_fifteen_minutes.TimeColumnMinutes,?) >= appointments.startime
AND time_fifteen_minutes.TimeColumnMinutes <= SUBTIME(appointments.endTime,"100")
WHERE appointments.bookedfor = ( SELECT bus_user_ID FROM business_users
WHERE URL_identifier = ?)
AND appointments.startDate = ?
)
AS NonAvailable
ON NonAvailable.A = time_fifteen_minutes.TimeColumnMinutes
WHERE NonAvailable.A IS NULL
AND store_open.day = DAYOFWEEK( ? )
AND store_open.b_user_ID = ( SELECT bus_user_ID
FROM business_users
WHERE URL_identifier = ?)
AND IF(STR_TO_DATE(?, "%Y-%m-%d") = CURRENT_DATE(),TimeColumnMinutes > CURRENT_TIME(),TRUE)');

The query so far as expected, I just need advice on testing edge cases.

The DDLs now:

CREATE TABLE `appointments` (
`apID` int unsigned NOT NULL AUTO_INCREMENT,
`userID` mediumint unsigned NOT NULL,
`bookedfor` mediumint unsigned NOT NULL COMMENT '≤ί άΫΪ▐ ΪύΊ ≤Ϊ▐Έύ άΊάή±▄÷ΎΊΪάώ Ϊά ±άΊΪίέΎ² ήώά ΪΎΊ ΎΏΎ▀ΎΊ ΆΈί▀ΊΎΊΪάώ Ϊά ±άΊΪίέΎ² Άάώ ίΉ÷άΊ▀όΎΊΪάώ ≤ΪΎ backend ΪΎΫ bookedfor business_user',
`appont_close_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` date NOT NULL,
`startime` time NOT NULL,
`endDate` date NOT NULL,
`endTime` time NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`deleted` tinyint unsigned DEFAULT NULL,
`cancelled` tinyint unsigned NOT NULL DEFAULT '0',
`timezone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Europe/Athens',
PRIMARY KEY (`apID`),
KEY `startDate` (`startDate`),
KEY `bookedfor` (`bookedfor`),
KEY `to_userID` (`userID`),
CONSTRAINT `appointments_bookedfor_ac466374_fk` FOREIGN KEY (`bookedfor`) REFERENCES `business_users` (`bus_user_id`),
CONSTRAINT `to_userID` FOREIGN KEY (`userID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1376 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `store_open` (
`store_open_ID` int NOT NULL AUTO_INCREMENT,
`b_user_ID` mediumint unsigned DEFAULT NULL,
`open_time` time NOT NULL,
`close_time` time NOT NULL,
`open_time_b` time DEFAULT NULL,
`close_time_b` time DEFAULT NULL,
`day` tinyint NOT NULL,
PRIMARY KEY (`store_open_ID`),
KEY `b_user_ID` (`b_user_ID`),
KEY `day` (`day`),
CONSTRAINT `store_open_b_user_ID_697f288c_fk` FOREIGN KEY (`b_user_ID`) REFERENCES `business_users` (`bus_user_id`),
CONSTRAINT `store_open_ibfk_1` FOREIGN KEY (`day`) REFERENCES `weekdays` (`dayID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=469 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='schedule.php: the weekly schedule of the business';

CREATE TABLE `time_fifteen_minutes` (
`TimeColumnMinutes` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Multiple Sub-directories in MySQL Data Directory (no replies)

Scott Limpert — Thu, 12 Dec 2024 21:09:53 +0000

I am migrating to MySQL Standard Edition 8.4. In my previous RDBMS environment I organized tables for different purposes into different folders, which I could access programmatically and manually by either specifying the full path of the table directly or first issuing a Change Directory command. How do I replicate this environment in MySQL and use it manually in MySQL Workbench and programmatically in Python?

Thank you for your kind assistance.

Many to many relationships (no replies)

Betty Calagoure — Thu, 17 Oct 2024 18:27:58 +0000

I'm setting up a database for a local organization. The organization puts on weekend retreats, the retreats are run by teams which are pulled from a list of previous participants. The people who attend the weekends are then added to the list from which they may be pulled to work on one or more weekends on different jobs. This information needed is 2-fold:
1) list of all participants who worked on a weekend sorted by which jobs each participant did
2) find a weekend with a list of who was doing what job

I've worked with databases before but nothing this complicated. My structure is this:

WEEKENDS
Weekend_ID Weekend_Date Location Job_ID

JOBS
Job_ID Job_NAME

PARTICIPANTS
Participant_ID First_Name Last_Name Email Weekend_ID

PARTICIPANTS_JOBS
PJ_ID Job_ID Participant_ID Weekend_ID

WEEKENDS_JOBS
Weekend_ID PJ_ID Job_ID

Does this make sense? Am I missing something? Is it normalized properly?

Thanks for your help

Which is best way of creating a database for each or one table in a database in MySQL? (1 reply)

Thirupathi K — Tue, 29 Oct 2024 06:44:18 +0000

We are working on IoT device, each device will stores about 20 mb of data each month and we have 20K and more IoT devices in the filed. we need storage for 7 years. So which is best way of creating database for each device or one database with one table or one database with multiple tables ?

Try to understand that all 20,000+ IoT devices have the same data and IoT with unique ID.

Each IoT device will store 70 columns of data and insert it every 1 minute. So, it will store 1,440 rows a day, 42,300 rows per month, 518,400 rows per year, and 3,628,800 rows per 7 years. This calculation is for only one device.

Storage =appr. 1.8 GB per 7 years per IoT device. For all devices = 35 TB of data.

My questions are:

How much data can be stored in a single database?
Which is better: creating a single database or multiple databases?
Are there any issues that will occur using a single database?
is it any fetching speed issue?

Existing tables adding foreign key constraints (no replies)

Matthew Lenz — Wed, 20 Dec 2023 19:11:21 +0000

imgurDOTcom/a/NVlFVl4

I've got the above set of tables ER diagram above.

members have many users
members have many approval_groups
approval_groups have many approval_group_users

approval_group_users.member_id must be of the same member as it's approval_group.

approval_group_users.user_id must be of the same member.

I can add foreign keys to satisfy all these constraints but it involves creating composite unique keys on user.member_id and user.id as well as approval_group.member_id and approval_group.id.

It just feels wrong creating unique keys that use the primary key though. I'm aware that this entire model might be improved by not putting member_id in the tables and using link tables but as soon as I say something like approval_group.name must be unique by member that all breaks down and I'm back to where I started.

Am I crazy in thinking that these composite keys using the primary key are the only way to implement this constraint? I could just say forget it not bother enforcing integrity with users and approval_groups with their members.

One other note is that I added approval_group_user.member_id to satisfy the constraints I need. These are a simplified examples of existing tables in a real application that has no FKs defined currently. Adding a new field to these tables is an easy change. Adding new tables would be prohibitive.

I'd love some feedback from the pros. Thanks!

Determining MAX Value (1 reply)

Stephen Dawson — Wed, 08 Nov 2023 16:01:18 +0000

Hi,

I have the following syntax returning the expected results.

SELECT
material.artist as "Artist",
material.title as "Title",
material.period as "Period",
material.style as "Style",
path.step as "Steps"
FROM material
INNER JOIN path ON material.id=path.material
ORDER BY period ASC, style ASC;

What I would like to do is obtain the maximum value in the path.step field. The desired outcome is to reduce the record set to only have a single record for each material.id based on its associated maximum value of path.material.

I have attempted to add MAX to path.step, but I am not successful in my syntax modification work.

Thank you in advance for your assistance.

Kindest Regards,
SHD

Designing tables for school management (no replies)

Mahesh Kumar — Tue, 15 Aug 2023 15:35:43 +0000

I am confused how to design the user table.

First i thought of doing like these.

Student
Basic student info with login
Academic details in other few tables

Teachers table with basic info and login
Other few tables linked with teachers

Parent table with basic info and login
Link the parent to child from other table

Admin table with basic info and login
Accountant table with basic info and login

It seems not good for me.

I decided to do like the following

Users table with basic info.
Login tables with login and roles (student teacher….)

Keep the login id in the users table as a foreign key.

Now separate table for student, teacher and parent. Keep the user id in student, teachers table

This one seems good for me. Is that a right design.

In users table
Id, user first name, last name, dob, gender

In login table
Id username password and role

Should i keep the address, phone no in users table? So i don’t need any extra table for admin and accountants, parents

In general, can a accountant see a phone number of a user? Should be hide those details programmatically?

This is the first time i am making a database design and confused. I dont want to keep everything in single table expose the info to others. At the same time, just for phone number and address should i create separate table for admin, accountants and parents?

Please help me with user design system

Table size vs amount (no replies)

Thomas Schmitt — Thu, 01 Jun 2023 22:58:57 +0000

Hello,

I do not have experience in DB design and it would be one terrible time/usage trade for me, to learn MySQL design considerations from zero for my first DB project since university and probably the last one in my life.

So I ask you just to propose optimal MySQL DB design to fill it from a web page for unlimited time (in best case my entire life).

Don’t think about use of the data, actual data will be different, but the structure is exact the same.

There will be a regular web forum from simple template with user registration. Each user register up to 10 weather stations and adds every day the same data, like temperature, pressure and so on (may be in the future we will require other values, like radiation) and we have different weather station types, eg one show Celsius and another Fahrenheit, I will define available values for each model.
Each user earns points by adding today’s values to his weather stations. How many points the user will get, depends on the values and model. For example a temperature from simple thermometer cost 5 points and temperature from weather balloon 10000 high cost 50 points.

At the end, I will run simple analytics about earned points, for example top 5 users with the most points this month. Another example could be a cake diagram for this week, if all earned points are 100% and how much points in percentage added each user.

My idea was to define a record for each weather station model, then create a main table for all users with unique number (id), his current points for today and registered records. Just after data submit, I will calculate current points and on midnight save this value in a separate table, with all users and collected points on that date.
Like I said, it is just an example to explain the task, collected weather data is only required to calculate user points and won’t be saved.

Is it an appropriate design for long lasting application? By this design I would create for the next year new table and summarize collected point per user for each week, each month and each year in 3rd table for long time statistics.
Or may be it would be better to create a separate table for each weather station model and then fill it every day with user id and earned points by this model? Or should each user get his own table? Or should I create a record with all possible data and just fill one table with date, user and record? Or may be one table with a separate column for auch possible value (or should I create such a table for every day and previous one save in Archiv?)

There are a lot of possibilities, what is your suggestion for my use case?

Thanks

Database Design for task monitoring system (no replies)

Prashant Sontale — Fri, 12 May 2023 11:46:27 +0000

I am designing the database table structure for the task monitoring system. I have a situation where each user can assign tasks to each other. For example, user A assigns a task to user B. User B can assign tasks to user C and so on.
I have designed the following tables and their columns

1. users table - stores all users of the system
UserId - int - Primary key- auto increment
Full Name - varchar
Email - varchar
username - varchar
password - varchar

2. tasks table - stores the tasks for each user
TaskId - int - primary key -auto increment
CreatedBy - int - foreign key - (UserId of users table)
AssignedTo - int - foreign key (UserId of users table)
TaskName - varchar
TaskDescrition - varchar
StartDate - date
EndDate - date
Status - varchar

Is this table design correct for storing the task details created by one user to another?

Best Practice For Uploading CSV Files (no replies)

Duck Duck — Thu, 06 Apr 2023 10:12:56 +0000

We have a customer control panel where customers can upload their products into our online shop. For each product, we also need to call a third party API. But what's the best practice for allowing users to upload their own CSV files?

1. Bulk upload the CSV file directly into the destination products table, then call the API, then update the data as needed.

2. Bulk upload the CSV file into a staging table, then call the API, then insert the row into the destination products table.

Change table name from Portuguese to English (no replies)

Sourav Dutta — Wed, 29 Mar 2023 13:48:40 +0000

Hello,

I have database where many tables name are in Portuguese. I want to change the tables name using query at one go.

Can any one please help me on this?

First I will have to identify the tables which language are Portuguese then I want to run one single query to change all the tables name in English using one query at one go.

problem with counting available seats in database (no replies)

Momcilo Raicevic — Sun, 26 Mar 2023 13:51:11 +0000

OK, I'm struggling with this part of my mule assingment: In case that person has virus symptoms you should put them in hospitals specially made for coronavirus where their capacity isn’t full. When a person goes into a hospital you should handle free spots at the hospital.

so, first I created my mule app, then I put the choice router on the basis of which I decide whether a patient will go to hospital_a, hospital_b or hospital_c. the criterion on the basis of which router decide where a specific patient will go is his id number (if the id is less than 333333333 he will go to hospital_a, if it is less than 666666666 to hospital_b, and if it is greater than that number he will go to hospital_c, which I put in default route). I wrote an expression properly in each of insert database fields and when I test it via arc it shows no errors. I think that part is ok.

Then I created database in mySQL called accounts in which i created three tables, each represent one hospital (hospital_a, hospital_b and hospital_c). I want the maximum number of available seats in each hospital to be 10, and if I try to insert an account when the number of available seats is 0, I want the program to transfer that account to one of the two remaining bases with available seats. if there are no available seats in any of the remaining two, I want it to receive a message, something like "all hospitals are full"

any idea?

Storing the phones of the users (no replies)

Dimitris Papageorgiou — Fri, 17 Mar 2023 12:50:45 +0000

I have two tables...the one stores appointments and the other stores users:

Besides the users who will book appointments from the site there are also users that will book an appointmnet from the phone, my problem is that I want to store somewhere the phones(a requirement) of these users.

CREATE TABLE `appointments` (
`apID` int unsigned NOT NULL AUTO_INCREMENT,
`Bookfrom` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT ',
`bookedfor` mediumint unsigned NOT NULL ,
`appont_close_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` date NOT NULL,
`startime` time NOT NULL,
`endDate` date NOT NULL,
`endTime` time NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`delete_back` tinyint unsigned DEFAULT NULL,
`delete_front` tinyint unsigned DEFAULT NULL,
`bookfromID` mediumint unsigned DEFAULT NULL,
`cancelled` tinyint unsigned NOT NULL DEFAULT '0',
`phone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `startDate` (`startDate`),
KEY `fk_bookFromID` (`bookfromID`),
KEY `bookedfor` (`bookedfor`),
CONSTRAINT `fk_bookedFor` FOREIGN KEY (`bookedfor`) REFERENCES `business_users` (`bus_user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_bookFromID` FOREIGN KEY (`bookfromID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `users` (
`user_ID` mediumint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`lastname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`usertype` tinyint unsigned NOT NULL,
`Reg_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`registr_hash` char(32) DEFAULT NULL,
`active` tinyint unsigned NOT NULL DEFAULT '0',
`reset_pass_selector` char(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`reset_pass_verifier` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`token_expire` int unsigned DEFAULT NULL,
`del_account_time` timestamp NULL DEFAULT NULL,
`FB_user` tinyint(1) DEFAULT NULL,
`Google_user` tinyint(1) DEFAULT NULL,
`FB_user_ID` bigint unsigned DEFAULT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`userPhone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`user_ID`),
KEY `fk_users_user_type1_idx` (`usertype`),
KEY `email` (`email`),
CONSTRAINT `fk_users_user_type1` FOREIGN KEY (`usertype`) REFERENCES `user_type` (`usertype_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=311 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

So:
Do you think I should create a separate_table for these phone_users(to keep their phones and names) or I should put them in the users table(and use ENUM for example to distinguish them from site registered users).

That is one thing...

The other is that I must somehow prevent a double entry of the same phone user.

So what do you propose for these two problems?

In the appointmnets table I have a phone column...so that each booked appointmnet has a phone associated with it(along with other data)...the problem with this approach is that the same phone number will be duplicated many times since the same phone user will book appointmnets many times within a timeframe (hair salon for example about 1/month)

So,to recap where do I store phone users w their name and phone....and how to prevent double entry?

When the business goes to enter in the form the name/phone of the phone user how can I reliable check in the DB that the user is not already there...that problem becomes more pertinent when for example the business writes John or john...that will refer to the same person--without a reliable DB check duplication of entry will occur in the DB

Distributed App Architecture Design (1 reply)

Konstantin Markov — Mon, 04 Dec 2023 06:22:38 +0000

Hi everyone.

I want to start with the fact that I couldn't find the right section for my question but I still really wanted to post it somewhere so I figured I'll do it in this sectoin. If any moderators see this please point me in the right direction.

I have a client which requires a distributed database architecture. Imagine you have an online shop and physical shops. Every physical shop has vending machines from which you can get different products. He wants the system to be able to work even offline - so that I can continue storing/updating the product quantity in each vending machine as physical sales continue even if the internet connection goes down. This would require to have a multi-master configuration with one DB which saves all data like user accounts, account balance, physical shops, vending machines product quantity, etc. I'll also need to have a DB in every physical store so that I can save vending machine's product quantity data locally too (so it's available all the time).

This led me to believe I need multi-master replication. But here my knowledge about this topic ends so I'd like to ask you to point me in the right direction. How do you think I could achieve this using MySQL? Would group replication be the way to go or there's some other way I'm not aware of. Thank you in advance.

Best,
Konstantin

synching erd model loses foreign key links (no replies)

jason w — Wed, 25 Jan 2023 02:00:50 +0000

I have an existing database where the design is frequently updated (new tables, addition of columns, removal of tables/columns, etc). I initially created the database writing ddl code and saving it as a script file. I’ve made several updates to the script and reran the script to recreate the db for the updates. I am using the workbench diagram/modeling tool to generate EER diagram.

So i have the existing model (mwb file) from before these new updates. All fk links are connected. When i go to synch model with my new db updates, for some reason a lot of fk links are gone/removed. Even with tables i made no changes to. In my scripts and the db, the fk constraints are there, but the model doesn’t recognize them or something. When i go thru the synch model process I am choosing “update model” during the model and db differences screen for the changes it recognizes. But even for some tables that didn’t change, the fk link is lost! And i can’t figure out why. Anyone experienced this behavior?

Authenticating MySql 8.0 Community edison against active directory (no replies)

shiva D — Wed, 09 Nov 2022 20:03:32 +0000

I did lot of research but didn't find solution to authenticate mySql 8.0 community edition againist ACtive Directory using LDAP. I found solutions using authentication plugins on Enterprise edistion but nothing for Community Edition. Any pointers towards the solution would be helpful.
Thanks,
Shiva

Finding value in ranges (no replies)

Ben D — Thu, 27 Oct 2022 18:13:21 +0000

Hi All,

I have a table named distribution_details with the following structure and am including just a small subset of rows to try and help explain my issue.

| id |	| max_days |	| pct_to_management_company |
-----------------------------------------------------
  10	  3	          95.00
  1	  10	          90.00
  4	  30	          80.00
  6       45              75.00

I need to get the value that is in pct_to_management_company column based on a value(calculated using TIMESTAMPDIFF() that returns the number of days) that falls within the max_days column

So if I get a value of 4 returned by TIMESTAMPDIFF() I would need to find the row where 4 would be in a specific range. In this instance where max_days > 3 AND max_days <= 10

There could be an infinite number of rows here and I'm just not sure how I can get a bunch of different ranges to see where this number would fall into. Would this even be possible with just a query?

Thanks,
Ben

About Find who create some tables Mysql RDS AWS (no replies)

Marco Antonio borges — Thu, 20 Oct 2022 18:34:57 +0000

Hi.

I need to know about the user/schema that created some tables in database production.

I tried to find this information in official documentation but I for tables, differently other objects like procedures, views, and triggers I can't.

About change the Definer in database Objects RDS AWS (no replies)

Marco Antonio borges — Thu, 20 Oct 2022 18:30:50 +0000

Hi.

I have some objects that were created with incorrect user/schema.

Then,
My question is:

Its possible to change the Definer, for example in
information_schema.views

which way is better- one or two tables? (no replies)

Simon Hiaubeng — Mon, 03 Oct 2022 07:28:02 +0000

There are a number of html files, which have tables I am to process. My application codes are to read a file, get the tables, and append the information to a table in MySQL. Problem is that some html files have EoF error, and some miss a couple tables.

I have created a MySQL table that has columns for filenames, primary_key, and 'is_good'. It looks like

filename_table
pk INT % autogenerated
filename VARCHAR(45)
is_good TINYINT % default 1: good
error_comment VARCHAR(180) % default null

And whenever a bad file is encountered, the 'is_good' is updated to be 0, and 'error_comment' are updated with some error message.

Or is it better to create a new table to store the error information and use foreign key to relate it to the filename_talbe? There shouldn't be many 'bad' html files in the source.

Multiple tables or one table with "identifier" (no replies)

John Smith — Sat, 17 Sep 2022 13:35:48 +0000

I have been working with DB2 databases for 30+ years but I am relatively new to MySQL.

I am in the VERY early stages of designing a web app with a MySQL backend. My customers will only be able to access the data via the web app. They will have no direct SQL access.

90% of the data will be unique by customer (guessing it will be about 20-30 tables) with a few shared "master" tables (guessing maybe half a dozen). At this point, I don't know how many customers I will have using this app or how many rows will be in the tables (database has not yet been designed).

My question is (for now ignoring size constraints...I doubt it will ever reach that level) would it be more efficient to have all of the customer's data in one set of tables with a customer identifier field in each table to separate the data or should I create unique table names by customer such as Customer1_contacts, Customer2_contacts?

It seems like the data retrieval would be much more efficient with unique tables but I don't know if that creates worse performance for the engine itself if a bunch of customers are hitting it at the same time.

Looking forward to learning which method to use and why. :)

Generating ERDs from Conceptual or Physical Design? (1 reply)

jason w — Mon, 29 Aug 2022 01:34:37 +0000

Hello, I am designing a new database (from scratch) for an application that is still in the UI design phase and development is just starting. I have gathered all the requirements that I can and have created a conceptual design doc (a word document) that lists all the entities, attributes, and relationships, with some rules, standards, etc. It is very incomplete since the app design is still being worked on. We are then creating an ERD based on changes we make to the design document.

I have yet to do any physical design, but was thinking I should build the DDL scripts (and create test data) for the entity set I have defined in the document so far. A lot of database design best practices say to complete the logical design in full before moving onto physical design but I am thinking this is outdated practices.

As far as keeping the ERD up to date and simplifying the maintenance of it, the process I have seems too much work and not simple (I update the conceptual document in Word and then the ERD is generated (by another person) based on changes to the Word doc. I was thinking maybe I should just start building ddl scripts in mySql workbench and then the ERD could possibly be autogenerated with the ERD tool (they are using draw i/o).
I could still maintain the document since I think it's good to have an overall written design, but I wanted to ask and see what everyone thought about this process and what are some of the common procedures for a brand new project (no existing database exists) in its early stages. Thought or tips anyone?

Thanks, J

General input on schema (no replies)

Michael Botsko — Mon, 18 Jul 2022 21:09:11 +0000

Schema: https://pastebin.com/KfEgqkJb

I've included explanations for the data and how they'll be used. I've tried my best to choose appropriate column types, normalize data where possible, add foreign keys correctly, etc but welcome any and all input.

I'm rewriting a decade-old java plugin I had written for Minecraft servers as a hobby. It uses mysql/maria and logs game event data and can potentially log millions of records in the activities table. This schema is essentially written from scratch.

Users download this app and provide their own MySQL databases so I can't always guarantee the environment but I want to be as smart about the schema as possible.

The above schema is my current progress with my rewrite. I have yet to add query-focused indexes to the activities table but everything else should be complete.

I don't really have any specific questions anymore but you might see things worth commenting on.

Employee attendance table design (2 replies)

Davor Geci — Sun, 04 Dec 2022 09:37:00 +0000

Hello guys,
my previous experience tells me that here in this forum are very experienced programmers and maybe some of you had more experience in this types of systems.

I'm creating an attendance app for a factory where workers will scan cards at starting a shift and ending their shift.
The database would be MySQL.
Workers are working in 3 shifts (from 6 to 14, from 14 to 22, and from 22 to 6) that rotate every week. They will punch only when they enter the shift and when they leave.
Some workers also can come to another shift and that will be over time. For example, they work from 6 to 14 and then they come again from, for example, from 18 to 20.

What would be your advice, which attendance table design would be a better choice:

1. One row for Check-in and Check-out, for example:
PunchID, EmployeeID, CheckInDateTime, CheckOutDateTime

2. One row for every Punch but workers need to select if they are coming or leaving, for example:
Code:
PunchID, EmployeeID, DateTime, CheckTypeID
(where CheckTypeID would be cIN or cOUT)

3. One row for every Punch, where I will guess in the StoredProcedure what is CheckIn and what CheckOut is, for example:
PunchID, EmployeeID, DateTime

If you have any experience in this type of system, your advice would be great!

Thanks, Davor

compound primary key with one column with autoincrement (1 reply)

Fernando Sanchez Cabarga — Thu, 26 May 2022 16:15:54 +0000

Hello everyone!,

This is my question: I have one table with a compound primary key. The primary key has one own column with autoincremet. The rest of the primary key columns comes from another tables, with its values. When I insert one new row, the autoincremet is advancing each time. I want that the autoincremet continue its value group by the other cloumns of the compound primary key. It that posible? Would yo help me?. Thank you very much

Use of Datatype (4 replies)

Arrie Schoeman — Wed, 24 Aug 2022 22:07:47 +0000

Good day.
I am new to MySQL but have some experience in MS Access where I created a database and now redo it in MySQL.
I have a question about INT(). When I select INT() I then enter a number in the bracket -INT(10), but as soon as I save the table the number and brackets disappear. It will only show INT. Why??
I am busy to create the tables and do not know if the field was set to the number in brackets or not.
Thanks

Database modelling example (1 reply)

Omar Echegaray — Wed, 19 Jan 2022 00:34:14 +0000

Any change you guys can help me solving this out?

"In a company, there are departments and investigation cases that can be marked with several tags in order to find them later. Journalists work at a department and can conduct investigations that include opening several interviews processes (one at a time) that has a duration where they basically interview people and collect data, a lot of data.

Given that:

An investigation case can be taken by only one department.
An investigation case (and its interviews) can be conducted by one or more journalists.
Activity within a department is measured by how many interview processes are taken within an investigation case.
Each interview carries a lot of content in text that is stored in a document database in the cloud and that there are also other formats like images, audios and videos stored in a cloud storage solution like AS3.
It is known that the total amount of registries (considering all the interviews) exceeds the millions.

The company uses an internal platform to conduct the interviews that tracks activity of the departments and its journalists using Mixpanel."

Duplication of Database per customer (1 reply)

Michael Keough — Tue, 07 Dec 2021 18:07:44 +0000

I have built a large database application that will will service 20 customers. Each customer services between 60 and 200 customers and each of their customers has between 50 and 1200 users.

I am trying to figure out if I should keep everything in one database or break each of the 20 primary customers into their own database with the same structure. On login I can determine what database to select for the client or sub client based on an organizational ID ie ORG id 205 would reference 205databaseName

Is this efficient or am I better off just keeping the data in one database structure and not breaking things out.

Complex Database Creation (5 replies)

Mary Poff-McDole — Sat, 31 Jul 2021 16:34:39 +0000

I am creating a complex database for Employee Scheduler Online. As the name implies I will be collecting information to allow a scheduler to schedule employees for a business.

I apologize for the lengthiness of this, but there is no simple way of explaining this.

There will obviously be more than one business in which I am connecting all areas using a Bus_UUID. The business will only get the information for their business if login Bus_UUID matches what is in the database. The business will never see the Bus_UUID and will only be able to see their created Bus_ID.

First is specific business information in which it is gathered how many weeks a schedule should be created, what day should scheduling begin, which week the scheduling should begin, the time type (standard/military) to be used for the business, minimum time off requirements for general employees/management, and lunch requirements. This part is already taken care of.

Next is shifts referred to as Business Hours. Their will be an option of 4 shifts for the business to be able to select from. In each shift the Executive chooses which shift should be scheduled, the earliest and latest hours employees can be scheduled, and business hours. The earliest time for each day is used to set a 24 hour day. I already have this part taken care of.

In each shift, the business will be able to put in sections only if the shift is selected to be used in Business Hours. Using the information gathered from shift information, a series of questions for the section is asked to the section name, day required, number of employees needed earliest/busiest/latest, earliest/latest time employee can be scheduled in the section, and earliest/latest time of busiest for section. I already have this part taken care of.

This leads to the question I am stuck on. From the Section information for each shift, I need to be able to display all items for Bus_UUID on a web form that shows section name (This part is taken care of, but I don't mind changing how if needed). What I need is to be able to have it in the database which sections the employee should be scheduled. On the display it is showing as checkboxes. If checked, I need the information to be entered in database under employee ID/Bus_UUID. If unchecked, I need it to be able to be deleted. How do I do this in a database?

Section information for each business is liable to be different. My intent is to allow up to 25 sections per business (if more is needed they will need to call me).

Second part of this question: I am creating a place in which business have the opportunity to have set hours for employees (businesses using this could save schedulers a tremendous amount of time). For example, say full time employees work 1 month in days and the 2nd month at nights, then repeat after this. With this being set hours, it is needed to know which section the employee will be working in. The selected checkboxes from employee section select needs to be able to be checked here (up to five sections in a week). If checked here, shouldn't be able to be removed from Employee Section Select. How do I do this in a database?

After I have this answer, I will be able to use it for Scheduler Shift Select/ section select (in which all 4 shifts if selected in Business hours will be available on one form along with all section information for shift).

The tables primary key for Employee Section select is a BIGINT and can be used to record, but needs to include Bus_UUID in selection.