Need advice on postrenal database?

Hi :3
Need to design a database of metro contactless cards(RFID tags) for this there are four tables with the following relationships.
Table cards
id_card - card number
cash - the current number of trips
station - the station where you last used it
time - time of last use
status - rabochey\not working

Table station - lists all of the subway stations.
Table history_add - history recharge cards
Table history - the history of the movement of media cards

4e2523250e44456b8fdd7bf9668e1726.png

Please give me advice on how to "normalize the data"(lead them to some sort of NF). And what would You have put in there :3
June 27th 19 at 15:31
2 answers
June 27th 19 at 15:33
cards
id (INT), status (BOOL), ...
stations
id (INT) name (VARCHAR), ...
operatioons
id (INT), station_id (INT), operation (INT) (+1/-1, etc), balance (INT), created (INT)...

station - the station where you last used it

time - time of last use

SELECT FROM `operatioons` WHERE `station_id` = {STATION_ID} ORDER_BY created DESC LIMIT 1

Displays the last operation with a map, from here you can take a station ID and the time when the card was used (have paid/passed the turnstile)
So now not four, but three tables? - candace.Durgan commented on June 27th 19 at 15:36
: Yes, why do You need 4? - nicklaus.Will commented on June 27th 19 at 15:39
: well, this is the type of course and said "the more the better." :| - candace.Durgan commented on June 27th 19 at 15:42
: I don't know, I doubt "the more - the better." - is it really better, usually the opposite, now it is a 3NF, that is enough.

You can still do so:

cards
id (INT), status (BOOL), ...
stations
id (INT) name (VARCHAR), ...
operatioons
id (INT) type_id(INT) station_id (INT), sum(INT), balance (INT), created (INT)...
operatioon_types
id (INT) name (VARCHAR), ...


The table operatioon_types will be only two records
1, Payments
2, Expenses
- nicklaus.Will commented on June 27th 19 at 15:45
:
I don't know, I doubt "the more - the better." - is it really better, usually the opposite,

And you can look for the card tables and tell it form and is it normal there at all? This one is already finished course.
35df804457ac4bacae8201a65e3cdf8e.png - candace.Durgan commented on June 27th 19 at 15:48
: well, that's another question, you need to add a new topic to get the answer. - nicklaus.Will commented on June 27th 19 at 15:51
: OK) - candace.Durgan commented on June 27th 19 at 15:54
June 27th 19 at 15:35
IMHO, a fully normalized form will contain just two tables:
stations (id, name);
events (card, event, station_id, sum, timestamp)

where is event: ('activation', 'parish', 'flow', 'lock', 'return', 'write-off')
But there is a sense in partial denormalization and add a field balance and if possible some operations with locked map, then the state field.
Still, three: even merchandise cards.
I mean in events - card is the id
plus the cards (card, card attributes...) - candace.Durgan commented on June 27th 19 at 15:38
In the original basis of attributes like not mortgaged. But if needed they did so, will need another table. - nicklaus.Will commented on June 27th 19 at 15:41
in the sense of "merchandise cards"? - candace.Durgan commented on June 27th 19 at 15:44
: well, for example the presence of the logo on the map, a unique number (which may not coincide with rfid id), all the anniversary series, etc.

Well, or more understandable - a wide range of RFID identifiers in the form of a solid, soft cards, key fobs of various types, labels, and blanks - the knowledge that this (map, key chain, ring) - can be useful

Well, very much in the running case, a card is copied - i.e. there may be more than one card with card_id which rfid - nicklaus.Will commented on June 27th 19 at 15:47
: as for me, the presence card_id, which may not coincide with the rfid id will not save you from copying. To track whether the original map and written history of passages through the turnstile and the history of replenishment. Ie the idea is this: if a person has been at one station, and after five or ten minutes he goes to another, which stopyatsot away from him means the card is stolen and it is blocked and not ignored. Also when copying, you can change the amount paid for passages. To do this, and there is a history of replenishment and current number of trips.
If the number of trips in the database differs from that recorded on the card - the card is also blocked. - candace.Durgan commented on June 27th 19 at 15:50
: to save and is not necessary. You just have to be able to store a lot of maps where the id matches

a typical and practical sample scripts, 100500, such as blocked may be "correct" map - followed by the scandal -))) - nicklaus.Will commented on June 27th 19 at 15:53
:
a typical and practical sample scripts, 100500, such as blocked may be "correct" map - followed by the scandal -)))

well this is the norm. In this case, it blocked both cards! And media original is coming to grandma's to grace the turnstile, he says:"What is this...?". And she's like,"Come into the office.". Friend goes to the cashier, where, of course, after many trials(without them anywhere :D) gets a new map with the current number of trips :-)
And rejoice with all of life, except for the guy with the fake card :( - candace.Durgan commented on June 27th 19 at 15:56
: but it does not interfere and does not help in terms of ideology, structure of the database

But additional attributes can help. For example the color of the card or the specificity of the type "student", "school". In the end of the second shield. For example, a characteristic signal when passing schoolboy... if visually at this time pret a bearded man - there is a reason... - delta89 commented on June 27th 19 at 15:59
: What's the point? If the reader you only came rfid-tag, you will be able to tell which of the ten duplicate cards it belongs to? - Kenyatta commented on June 27th 19 at 16:02
in Ukraine there is no school cards, students identity cardboard and their missing grandmother on preferential turnstiles, after checking the relevance of the date.
About the color, it is all the same, green.
I have base cards with a accumulation of trips and it is only by metro, and students an unlimited MetroCard. - candace.Durgan commented on June 27th 19 at 16:05
: that is the student unlimited travel - the same point of monetization for rogues.
And yeah, it's coursework -) - delta89 commented on June 27th 19 at 16:08
:
And yeah, it's coursework -)

Well, you can dream up :D
What else would you recommend to invent, to increase the number of tables?
PY.Sy. if not difficult - look in the comments of a previous answer. There's a map table with a ready course. I can say what there is NF and whether it is there at all? :D - candace.Durgan commented on June 27th 19 at 16:11
By the way, that's just the rate card (standard, school, student, pension, parliamentary) can be stored in its attributes. And the color, taste and smell keep does not make sense. - delta89 commented on June 27th 19 at 16:14
: Course not I give, not sell)

It is important that it should be at least one separate table - candace.Durgan commented on June 27th 19 at 16:17
:
Course not I give, not sell)

And no one asked to do it for me. Just to advise, what else would you include to look at the map tables in the comments of the previous answer. -) - delta89 commented on June 27th 19 at 16:20
: view is not clear where - is lazy -)
And so to suspend the cash for the sale of the cards (+1 table), cashiers (+1 table) the sales journal cards (cash, cashier, card) - another +1

Then you can implement not pipestone options when the map X travel each pass -1 trip, and the amount on the card XX money and the price of the trip depends on the time of day and day of the week

Well, all sorts of options such as "second visit within the hour - half-price" - Kenyatta commented on June 27th 19 at 16:23
: thank you :)
I will think :D - candace.Durgan commented on June 27th 19 at 16:26
: what is there to think? urgently to patent and sell the Metropolitan)) - delta89 commented on June 27th 19 at 16:29
: yeah, they got to the end of the year, a coup planned :))
Want from tokens to give and to take them on a plastic card. And then I will fly with "their" ideas :D - candace.Durgan commented on June 27th 19 at 16:32

Find more questions by tags SQLSQLite