Database

Re: Best practices for database tables.

17-Jul-19 23:17

My gut feel says that you need to have a table for the parts themselves - I doubt there is only one machine that uses Pins or Clips or that size bolt.

Part
- PartID - a unique identifier (int or guid)
- PartName
- Other information as required

Parts go into Bins and I'm assuming here that each bin can only contain one type of Part so

Bin
- Bin_ID - unique identifier
- Drawer_ID - Foreign key to Drawer table
- Part_ID - Foreign key to the Part table
- Information about the bin e.g. number of parts in this bin

Bins go into Drawers but note that the Drawer table doesn't reference the Bins, the Bins reference the Drawer, it's "owner" if you like. The same applies to the Drawer referencing it's Container

Drawer
- Drawer_ID
- Container_ID - Foreign key to Container table
- Other info about the Drawer e.g. Machine reference?

Container
- Container_ID
- Other info about the container e.g. location

It's very easy to over-engineer database design. For example I'm tempted to introduce a Machine table but that's probably over-doing it.

You might find these links helpful too:
Database Design Tutorial for Beginners - William Vincent[^]
How to Design a SQL Database[^]

Mycroft Holmes18-Jul-19 11:57

Mycroft Holmes

18-Jul-19 11:57

Pick a database structure that reflects your requirements and study the layout. http://www.databaseanswers.org/data_models/[^]

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Best practices for database tables.

RLD7121-Jul-19 3:37

RLD71

21-Jul-19 3:37

Thank you both. I looked at the links and they will help a lot. I think I trying to make it harder than it needs to be..I guess that is the engineer in me. Big Grin | :-D

structure question

Joan M11-Jul-19 1:30

Joan M

11-Jul-19 1:30

Hi all,

I'm thinking on making a small application to keep track of my working hours, billable or not...

I'd install it in my NAS to be able to access it from everywhere.

The database will be a MySQL database.

I'll do the programming in PHP (don't know any other thing for web).

I've been far away from databases for a long time and wanted to make a question about the right data structure...

I plan to have:

------------------
Table 1: CUSTOMERS
------------------
(PK) ID : autonumeric
Name : string
Address : string

------------------
Table 2: PROJECTS (1 customer can have N projects)
------------------
(PK) ID : autonumeric
Customer ID : number
Description : string

------------------
Table 3: TASKS_IN_PROJECT (1 project will have N tasks).
------------------
(PK) ID : autonumeric
Project ID : number
Person ID : number
Description : string
Date : timeDate
Start time : time
End time : time
Price per hour : number

------------------
Table 4: TASKS (N Tasks_In_Table will have 1 task).
------------------
(PK) ID : autonumeric
Description : string

------------------
Table 5: PEOPLE (N Tasks_In_Project will have 1 person).
------------------
(PK) ID : autonumeric
Person name : string

------------------
Table 6: EXPENSES_IN_PROJECT (1 project will have N expenses).
------------------
(PK) ID : autonumeric
Project ID : number
Note : string
Date : timeDate
Cost : number

------------------
Table 7: EXPENSES (N Expenses_In_Project will have 1 Expense).
------------------
(PK) ID : autonumeric
Description : string

My idea is having this database structure and then make a small PHP web page that would allow me to introduce customers, projects assigned to customers, tasks and persons for each project (those should be available from a combobox selecting from a list of available tasks (tasks table) and people (people table)), and also I would like to be able to add expenses to the project too in the same way... those expenses should be selected from a list coming from the Expenses table...

Do you think this is a good structure?

Have I done something terrible?

My objectives are:

1. storing all the worked hours.
2. being able to get some reports (total hours in a project, lists of tasks done...)

Thank you very much for your time and advice!

Re: structure question

Eddy Vluggen11-Jul-19 2:01

11-Jul-19 2:01

Joan M wrote:
Have I done something terrible?

No, it's not VB6 nor JavaScript.

Joan M wrote:
Do you think this is a good structure?

Decent

Just a few questions;

Joan M wrote:
(N Tasks_In_Table will have 1 task)

I don't see that table's structure?

Joan M wrote:
Price per hour : number

Are you going to lookup a price and enter it manually for every task? How about a table with base-tasks and pricing to choose from? Perhaps with an additional column for discounts or extra?

Also, you use autonumbers for your keys; That means you can enter a customer twice, without problems. I'd prefer a unique constraint on the autonumber, and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project. It'd also mean that a project would require a unique constraint on its autonumber for quick reference, but also a decent PK; the combination of the username and the projectname.

--edit
Free tip; if you're unsure about a structure, populate your example with mock-data and try some queries Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Re: structure question

CHill6018-Jul-19 1:30

18-Jul-19 1:30

Quote:
and a PK on the customers name. It's a nice way to ensure that there's a single unique identifyable customer for each project.

It's nice until you end up with two customers with the same name! Laugh | :laugh:

In my case I also can't use part of the address as I have a Father/Son scenario with the same name living at the same address (besides, people do move house occasionally).

So we have autonumbers - the onboarding process does the necessary checks for duplication

Re: structure question

Eddy Vluggen18-Jul-19 22:53

Query numbering in groups in access database ?

18-Jul-19 22:53

CHill60 wrote:
It's nice until you end up with two customers with the same name!

You, like the computer, and the cashier, need to be able to distinguish between the two, even in a listview. Wouldn't want to bill the father for the son's purchases Smile | :)

CHill60 wrote:
So we have autonumbers - the onboarding process does the necessary checks for duplication

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 24584674-Jul-19 23:50

Re: Query numbering in groups in access database ?

4-Jul-19 23:50

in sql server there is a group problem statement: DENSE_RANK() OVER (ORDER BY TABLUONGNV3.HOVATEN) AS STT

How to write in sql access issues in groups? I'm not writing well: (Select Count (*) FROM [TABLUONGNV3] as Temp WHERE [Temp].[MANHANVIEN] < [TABLUONGNV3].[MANHANVIEN]) + 1 AS STT

SQL

SELECT (Select Count (*) FROM [TABLUONGNV3] as Temp WHERE [Temp].[MANHANVIEN] < [TABLUONGNV3].[MANHANVIEN]) + 1 AS  STT, TABLUONGNV3.HOVATEN, TABNHANVIEN.IDNV, TABLUONGNV3.MACANV, TABNHANVIENCA.CANV, TABLUONGNV3.N01, TABLUONGNV3.N02, TABLUONGNV3.N03, TABLUONGNV3.N04, TABLUONGNV3.N05, TABLUONGNV3.N06, TABLUONGNV3.N07, TABLUONGNV3.N08, TABLUONGNV3.N09, TABLUONGNV3.N10, TABLUONGNV3.N11, TABLUONGNV3.N12, TABLUONGNV3.N13, TABLUONGNV3.N14, TABLUONGNV3.N15, TABLUONGNV3.N16, TABLUONGNV3.N17, TABLUONGNV3.N18, TABLUONGNV3.N19, TABLUONGNV3.N20, TABLUONGNV3.N21, TABLUONGNV3.N22, TABLUONGNV3.N23, TABLUONGNV3.N24, TABLUONGNV3.N25, TABLUONGNV3.N26, TABLUONGNV3.N27, TABLUONGNV3.N28, TABLUONGNV3.N29, TABLUONGNV3.N30, TABLUONGNV3.N31, TABNHANVIENCA.TIENMOTCA, TABLUONGNV3.TONGLUONG, TABLUONGNV3.PHAT, TABLUONGNV3.TAMUNG, TABLUONGNV3.THUCNHAN, TABLUONGNV3.GHICHU 
FROM TABNHANVIENCA INNER JOIN (TABNHANVIEN INNER JOIN TABLUONGNV3 ON TABNHANVIEN.IDNV = TABLUONGNV3.MANHANVIEN) ON TABNHANVIENCA.IDCANV = TABLUONGNV3.MACANV 
WHERE TABLUONGNV3.LUONGTHANG = #06/01/2019#
ORDER BY TABLUONGNV3.HOVATEN;

You see the attached file: http://www.mediafire.com/file/pajlr19fwde8r9l/Query_numbering.xls/file

CHill6010-Jul-19 7:51

Re: Query numbering in groups in access database ?

10-Jul-19 7:51

As you are not explaining this very well try the following:

1. Simplify your query down to the bare bones to demonstrate your problem e.g. remove all but one of the columns from TABLUONGNV3 from the select.

2. Supply us with some sample data for each of the tables left in your simplified query

3. Provide the results that you expect from the sample data provided.

Don't link to files - we won't download them, besides mediafire is a blocked site on my work computer.

Member 245846710-Jul-19 17:57

Re: Query numbering in groups in access database ?

10-Jul-19 17:57

This site has no support for sending attachments, so I use mediafire to send attachments, if your computer is blocked you can bypass the firewall using psiphon3.exe software, you can download this software at address Download Psiphon 3 for PC Windows 7/8/8.1/10 or XP Laptops
Soon receive your feedback

CHill6010-Jul-19 21:38

How To Fix SQL Database Error 824 ?

10-Jul-19 21:38

No - I cannot bypass the firewall using some dodgy download. What makes you think our corporate security is so weak it would allow me to do that Laugh | :laugh:

Why not just do what thousands of other members do - type in the details into a post?

Member 24584673-Jul-19 22:10

3-Jul-19 22:10

My data SQL Server is reported this error, How does this error fix you ?
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x5555aaaa). It occurred during a read of page (1:210768) in database ID 7 at offset 0x00000066ea0000 in file 'G:\Database\MyData.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
During redoing of a logged operation in database 'MyData', an error occurred at log record ID (30182:834:13). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Could not open new database 'MyData'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

Richard MacCutchan3-Jul-19 23:44

Richard MacCutchan

3-Jul-19 23:44

You need to do what the message tells you and restore the database from your backup. It is impossible from the above to know what caused the page error.

Eddy Vluggen4-Jul-19 2:49

https://support.microsoft.com/en-us/help/2015756/how-to-troubleshoot-msg-824-in-sql-server[^]

4-Jul-19 2:49

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 24584674-Jul-19 17:40

4-Jul-19 17:40

The information you provided me cannot correct the error

Victor Nijegorodov4-Jul-19 20:20

Victor Nijegorodov

4-Jul-19 20:20

Which actions from "Resolution" section did you perform?
What results did you get?

Member 24584674-Jul-19 23:49

4-Jul-19 23:49

I do not manipulate anything on my data

Victor Nijegorodov4-Jul-19 23:52

Victor Nijegorodov

4-Jul-19 23:52

Member 2458467 wrote:
I do not manipulate anything on my data

Richard MacCutchan5-Jul-19 1:47

Richard MacCutchan

5-Jul-19 1:47

You need to go back to the error message and follow the suggestions there. This is not a programming problem that anyone here can fix for you.

Eddy Vluggen5-Jul-19 1:04

5-Jul-19 1:04

Show us your create DB statement Smile | :)

--edit
And what kind of drive is G?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 245846718-Jul-19 18:21

18-Jul-19 18:21

It can't fix it right ?

Eddy Vluggen18-Jul-19 22:54

18-Jul-19 22:54

How do you know? Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.