Witam serdecznie,
Mam takie tabele:
CREATE TABLE `psAdmin` (
`id_admin` int(10) UNSIGNED NOT NULL,
`enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`company_id` int(11) NOT NULL,
`username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`salt` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`last_logged_in_date` datetime NOT NULL,
`valid_account_in_time` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`expiration_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `psAdminDetails` (
`id_admin_details` int(10) UNSIGNED NOT NULL,
`id_admin` int(10) UNSIGNED NOT NULL,
`name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`company_name` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
`nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`country_id` int(11) NOT NULL DEFAULT '0',
`provincial_id` int(11) NOT NULL DEFAULT '0',
`avatar` varchar(65) COLLATE utf8_unicode_ci DEFAULT NULL,
`level` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`notes` text COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `psAdminLoginAttempts` (
`id_admin_login_attempts` int(10) UNSIGNED NOT NULL,
`id_admin` int(10) UNSIGNED NOT NULL,
`time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `psAdminLoginHistory` (
`id_admin_history` int(10) UNSIGNED NOT NULL,
`id_admin` int(10) UNSIGNED NOT NULL,
`time` datetime NOT NULL,
`ip` varchar(25) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `psCompany` (
`id_company` int(10) UNSIGNED NOT NULL,
`enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`name` varchar(85) COLLATE utf8_unicode_ci DEFAULT NULL,
`nip` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`notes` text COLLATE utf8_unicode_ci,
`street` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`post_code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`country_id` int(11) NOT NULL DEFAULT '0',
`provincial_id` int(11) NOT NULL DEFAULT '0',
`contact_person` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`www` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone_number` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
`date_of_registration` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `psProvincial` (
`provincial_id` int(10) UNSIGNED NOT NULL,
`name` varchar(35) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `psAdmin`
ADD PRIMARY KEY (`id_admin`);
ALTER TABLE `psAdminDetails`
ADD PRIMARY KEY (`id_admin_details`);
ALTER TABLE `psAdminLoginAttempts`
ADD PRIMARY KEY (`id_admin_login_attempts`);
ALTER TABLE `psAdminLoginHistory`
ADD PRIMARY KEY (`id_admin_history`);
ALTER TABLE `psCompany`
ADD PRIMARY KEY (`id_company`);
ALTER TABLE `psProvincial`
ADD PRIMARY KEY (`provincial_id`);
ALTER TABLE `psAdmin`
MODIFY `id_admin` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `psAdminDetails`
MODIFY `id_admin_details` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `psAdminLoginAttempts`
MODIFY `id_admin_login_attempts` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `psAdminLoginHistory`
MODIFY `id_admin_history` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `psCompany`
MODIFY `id_company` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `psProvincial`
MODIFY `provincial_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;
tabele mają następujące funkcje:
- psAdmin - tabela loginów administratorów
- psAdminDetails - tabela danych szczegółowych administratorów
- psAdminLoginAttempts - tabela z próbami logowania administratorów
- psAdminLoginHistory - tabela z historią logowania administratorów
- psCompany - baza firm. Firm może być nieskończenie wiele. Do każdej firmy można przypisać administratorów
- psProvincial - województwa
Czy dodalibyście jeszcze jakieś klucze (np. obce)? Jeśli tak, to gdzie?
Jest sens łączyć:
1. psAdminLoginAttempts.id_admin = psAdmin.id_admin
2. psAdminLoginHistory.id_admin = psAdmin.id_admin
3. psAdmin.company_id = psCompany.id_company
4. psAdminDetails.id_admin = psAdmin.id_admin
5. psAdminDetails.provincial_id = psAdmin.id_admin
6. psAdminLoginAttempts.id_admin = psAdmin.id_admin
7. psAdminLoginHistory.id_admin = psAdmin.id_admin
kluczami obcymi?